Modifying tables#
Use the ALTER TABLE command.
We’ll use the following table in the examples below:
mydb=> DROP TABLE beverages;
DROP TABLE
mydb=> CREATE TABLE beverages (
mydb(> name text,
mydb(> price numeric(5,2),
mydb(> serving_temp text
mydb(> );
CREATE TABLE
mydb=> INSERT INTO beverages VALUES ('Tea', 9.99, 'hot'), ('Lemonade', 5.50, 'cool');
INSERT 0 2
mydb=> SELECT * FROM beverages;
name | price | serving_temp
----------+-------+--------------
Tea | 9.99 | hot
Lemonade | 5.50 | cool
(2 rows)
1. Adding a Column#
mydb=> ALTER TABLE beverages ADD COLUMN serving_quantity_ml integer DEFAULT 350;
ALTER TABLE
mydb=> SELECT * FROM beverages;
name | price | serving_temp | serving_quantity_ml
----------+-------+--------------+---------------------
Tea | 9.99 | hot | 350
Lemonade | 5.50 | cool | 350
(2 rows)
The new column is initially filled with null or whatever value is given in the DEFAULT clause.
You can include constraints and other options in the column description (just like in CREATE TABLE).
2. Removing a Column#
mydb=> ALTER TABLE beverages DROP COLUMN serving_quantity_ml;
ALTER TABLE
mydb=> SELECT * FROM beverages;
name | price | serving_temp
----------+-------+--------------
Tea | 9.99 | hot
Lemonade | 5.50 | cool
(2 rows)
The column’s data and constraints disappear. If the column is referenced by a foreign key constraint in another table, an error is raised unless you add a CASCADE clause.
3. Adding a Constraint#
Use the table constraint syntax:
mydb=> ALTER TABLE beverages ADD UNIQUE (name, serving_temp);
ALTER TABLE
mydb=> INSERT INTO beverages VALUES ('Tea', 9.50, 'hot');
ERROR: duplicate key value violates unique constraint "beverages_name_serving_temp_key"
DETAIL: Key (name, serving_temp)=(Tea, hot) already exists.
To add a not-null constraint (can’t be written as a table constraint) use:
mydb=> ALTER TABLE beverages ALTER COLUMN name SET NOT NULL;
ALTER TABLE
mydb=> INSERT INTO beverages (price, serving_temp) VALUES (9.50, 'hot');
ERROR: null value in column "name" of relation "beverages" violates not-null constraint
DETAIL: Failing row contains (null, 9.50, hot).
The constraint will be checked immediately, so the table data must satisfy it before it can be added.
4. Removing a Constraint#
You’ll need to know the constraint’s name. The psql command \d tablename can help.
mydb=> \d beverages
Table "public.beverages"
Column | Type | Collation | Nullable | Default
--------------+--------------+-----------+----------+---------
name | text | | not null |
price | numeric(5,2) | | |
serving_temp | text | | |
Indexes:
"beverages_name_serving_temp_key" UNIQUE CONSTRAINT, btree (name, serving_temp)
mydb=> ALTER TABLE beverages DROP constraint beverages_name_serving_temp_key;
ALTER TABLE
mydb=> INSERT INTO beverages VALUES ('Tea', 9.50, 'hot');
INSERT 0 1
mydb=> SELECT * FROM beverages; -- duplicate ('Tea', 'hot') pairs
name | price | serving_temp
----------+-------+--------------
Tea | 9.99 | hot
Lemonade | 5.50 | cool
Tea | 9.50 | hot
Add CASCADE to drop a constraint that something else depends on.
To drop a NOT NULL constraint (can’t be named) use:
mydb=> ALTER TABLE beverages ALTER COLUMN name DROP NOT NULL;
ALTER TABLE
mydb=> INSERT INTO beverages (price, serving_temp) VALUES (9.50, 'hot');
INSERT 0 1
mydb=> SELECT * FROM beverages; -- a row has NULL name
name | price | serving_temp
----------+-------+--------------
Tea | 9.99 | hot
Lemonade | 5.50 | cool
Tea | 9.50 | hot
| 9.50 | hot
(4 rows)
5. Changing a Column’s Default Value#
This won’t affect existing rows, only future insertions:
mydb=> ALTER TABLE beverages ALTER COLUMN name SET DEFAULT 'default_name';
ALTER TABLE
mydb=> INSERT INTO beverages (price, serving_temp) VALUES (9.50, 'hot');
INSERT 0 1
mydb=> SELECT * FROM beverages;
name | price | serving_temp
--------------+-------+--------------
Tea | 9.99 | hot
Lemonade | 5.50 | cool
Tea | 9.50 | hot
| 9.50 | hot
default_name | 9.50 | hot
(5 rows)
To remove a default value, use:
mydb=> ALTER TABLE beverages ALTER COLUMN name DROP DEFAULT;
ALTER TABLE
mydb=> INSERT INTO beverages (price, serving_temp) VALUES (9.50, 'hot');
INSERT 0 1
mydb=> SELECT * FROM beverages;
name | price | serving_temp
--------------+-------+--------------
Tea | 9.99 | hot
Lemonade | 5.50 | cool
Tea | 9.50 | hot
| 9.50 | hot
default_name | 9.50 | hot
| 9.50 | hot
(6 rows)
DROP DEFAULT is equivalent to setting the default to null.
6. Changing a Column’s Data Type#
mydb=> ALTER TABLE beverages ALTER COLUMN price TYPE money;
ALTER TABLE
mydb=> SELECT * FROM beverages LIMIT 3;
name | price | serving_temp
----------+-------+--------------
Tea | $9.99 | hot
Lemonade | $5.50 | cool
Tea | $9.50 | hot
(3 rows)
The TYPE type_name syntax only works if all existing entries in the column can be implicitly converted to the new type. For more complex conversions, add a USING clause to specify how to compute new values from the old:
mydb=> CREATE TYPE relative_temperature AS ENUM ('cold', 'cool', 'room', 'warm', 'hot');
CREATE TYPE
mydb=> ALTER TABLE beverages ALTER COLUMN serving_temp TYPE relative_temperature;
ERROR: column "serving_temp" cannot be cast automatically to type relative_temperature
HINT: You might need to specify "USING serving_temp::relative_temperature".
mydb=> ALTER TABLE beverages ALTER COLUMN serving_temp TYPE relative_temperature
mydb-> USING CAST (serving_temp AS relative_temperature);
ALTER TABLE
mydb=> INSERT INTO beverages VALUES ('Milk', 7.5, 'cool');
INSERT 0 1
mydb=> INSERT INTO beverages VALUES ('Water', 0.5, 'icy');
ERROR: invalid input value for enum relative_temperature: "icy"
LINE 1: INSERT INTO beverages VALUES ('Water', 0.5, 'icy');
^
PostgreSQL will attempt to convert default values and constraints as well, but it’s often better to drop them first, and add suitably modified ones afterwards.
7. Renaming a Column#
mydb=> ALTER TABLE beverages RENAME COLUMN price TO unit_price;
ALTER TABLE
mydb=> SELECT * FROM beverages LIMIT 3;
name | unit_price | serving_temp
----------+------------+--------------
Tea | $9.99 | hot
Lemonade | $5.50 | cool
Tea | $9.50 | hot
(3 rows)
8. Renaming a Table#
mydb=> ALTER TABLE beverages RENAME TO drinks;
ALTER TABLE
mydb=> SELECT * FROM beverages;
ERROR: relation "beverages" does not exist
LINE 1: SELECT * FROM beverages;
^
mydb=> SELECT * FROM drinks LIMIT 3;
name | unit_price | serving_temp
----------+------------+--------------
Tea | $9.99 | hot
Lemonade | $5.50 | cool
Tea | $9.50 | hot
(3 rows)