Summary: in this tutorial, we will show you how to use the PostgreSQL ADD COLUMN statement to add one or more columns to an existing database table.
Introduction to the PostgreSQL ADD COLUMN statement
To add a new column to an existing table, you use the ALTER TABLE ADD COLUMN statement as follows:
1 2 | ALTER TABLE table_name ADD COLUMN new_column_name data_type; |
Let’s examine the statement in a greater detail.
- First, specify the table that you want to add a new column in the
ALTER TABLEclause. - Second, indicate the column name with its attribute such as data type, default value, etc., in the
ADD COLUMNclause.
When you add a new column to the table, PostgreSQL appends it at the end of the table. PostgreSQL has no option to specify the position of the new column in the table.
To add multiple columns to an existing table, you use multiple ADD COLUMN clauses in the ALTER TABLE statement as follows:
1 2 3 4 5 | 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; |
PostgreSQL ADD COLUMN examples
The following CREATE TABLE statement creates a new table named customers with two columns: id and customer_name:
1 2 3 4 | CREATE TABLE customers ( id SERIAL PRIMARY KEY, customer_name VARCHAR NOT NULL ); |
To add the phone column to the customers table, you use the following statement:
1 2 | ALTER TABLE customers ADD COLUMN phone VARCHAR; |
The following statement adds the fax and email columns to the customers table:
1 2 3 | ALTER TABLE custoemr ADD COLUMN fax VARCHAR, ADD COLUMN email VARCHAR; |
The following command describes the customers table structure.
1 | #\d customers |
1 2 3 4 5 6 7 8 9 10 | Table "public.customers" Column | Type | Modifiers ---------------+-------------------+-------------------------------------------------------- id | integer | not null default nextval('customers_id_seq'::regclass) customer_name | character varying | phone | character varying | fax | character varying | email | character varying | Indexes: "customers_pkey" PRIMARY KEY, btree (id) |
As you see, we have the phone, fax, and email columns were added at the end of the column list of the customers table.
Add a column with NOT NULL constraint to a table that has data
Let’s insert data into the customers table.
1 2 3 4 5 | INSERT INTO customers (customer_name) VALUES ('Apple'), ('Samsung'), ('Sony'); |
Suppose you want to add the contact_name column to the customers table:
1 2 | ALTER TABLE customers ADD COLUMN contact_name VARCHAR NOT NULL; |
PostgreSQL issued an error:
1 | ERROR: column "contact_name" contains null values |
This is because the contact_name column has the NOT NULL constraint. When PostgreSQL added the column, this new column takes the NULL value, which violates the NOT NULL constraint.
To solve this problem…
First, you need to add the column without the NOT NULL constraint.
1 2 | ALTER TABLE customers ADD COLUMN contact_name VARCHAR; |
Second, update the values for the contact_name column.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | UPDATE customers SET contact_name = 'John Doe' WHERE ID = 1; UPDATE customers SET contact_name = 'Mary Doe' WHERE ID = 2; UPDATE customers SET contact_name = 'Lily Bush' WHERE ID = 3; |
Third, set the NOT NULL constraint for the contact_name column.
1 2 | ALTER TABLE customers ALTER COLUMN contact_name SET NOT NULL; |
Another way to solve the problem is to…
First, add the column with the default value.
1 2 | ALTER TABLE customers ADD COLUMN contact_name NOT NULL DEFAULT 'foo'; |
Second, update the contact data.
Third, remove the default value from the column.
1 2 3 | ALTER TABLE customers ALTER COLUMN contact_name DROP DEFAULT; |
In this tutorial, we have shown you how to use the PostgresSQL ADD COLUMN statement to add one or more columns to a table.