August 18, 2019
If you already have a table created in Postgres, how can you modify it to add a new column?
Similar to adding constraints after a table has been created, adding a column after table creation also takes advantage of the
ALTER TABLE command - this time using
ADD COLUMN instead of
Credit to Postgres Tutorial for the great tutorial which served as inspiration for the examples in this post1
ALTER TABLE table_name ADD COLUMN new_column_name data_type [constraint];
To add multiple columns, comma separate the rows
ALTER TABLE table_name ADD COLUMN new_column_name_1 data_type [constraint], ADD COLUMN new_column_name_2 data_type [constraint], […] ADD COLUMN new_column_name_n data_type [constraint],
In this way - each row is added very similarly to how you might within a
The major difference is that unlike the
CREATE - each column is prefixed with its own
ADD COLUMN command:
CREATE TABLE customers ( id SERIAL PRIMARY KEY, customer_name VARCHAR NOT NULL );
Imagine we had a
customers table with only an
id column. Now we want to add
customer_name as a required field for each new record we add to our table. If we remembered to include the column on create, we would do something like above, where we mark it as
In this case, however, we now have records in the table, and adding that constraint at this point will result in violations.
To handle this situation, two good options are available:
Personally - I prefer the former approach because it will be more clear whether the updates were sufficient, since Postgres will reject the constraint if they weren’t.
ALTER TABLE customers ADD COLUMN customer_name VARCHAR NOT NULL
If we do just this, we’ll get the error discussed above:
ERROR:column “contact_name” contains null values
ALTER TABLE customers ADD COLUMN customer_name VARCHAR NOT NULL UPDATE customers SET contact_name = ‘John Doe’ WHERE ID = 1; UPDATE customers SET contact_name = ‘Mary Doe’ WHERE ID = 2; […] ALTER TABLE customers ALTER COLUMN contact_name SET NOT NULL;
ALTER TABLE customers ADD COLUMN customer_name VARCHAR NOT NULL DEFAULT ‘UNKNOWN’ UPDATE customers SET contact_name = ‘John Doe’ WHERE ID = 1; UPDATE customers SET contact_name = ‘Mary Doe’ WHERE ID = 2; […] ALTER TABLE customers ALTER COLUMN contact_name DROP DEFAULT;
Returning to the original question, do we know how to answer it?
How do we modify an existing Postgres table to add a new column? When you need to add a column to a table in Postgres that already exists, use the
ALTER TABLE … ADD COLUMN syntax.
Furthermore, if the new column requires a constraint - two approaches can help:
Thanks for reading! My name's Stephen Weiss. I live in Chicago with my wife, Kate, and dog, Finn.
Click here to see the archives of my weeks in review and sign up yourself!