PostgreSQL – ADD COLUMN
PostgreSQL has an ADD COLUMN statement to add one or more columns to an existing database table. To add a new column to an existing table, you use the ALTER TABLE ADD COLUMN statement.
Syntax: ALTER TABLE table_name ADD COLUMN new_column_name data_type;
Let’s analyze the above syntax:
- First, specify the table that you want to add a new column in the ALTER TABLE clause.
- Second, indicate the column name with its attribute such as data type, default value, etc., in the ADD COLUMN clause.
While adding a new column to the table, PostgreSQL appends it at the end of the table. PostgreSQL has no option to set the position of the new column in the table.
Example 1:
First, we create a table (say, village
), with village_id
and village_name
columns using the below statement:
CREATE TABLE village( village_id SERIAL PRIMARY KEY, village_name VARCHAR NOT NULL );
Now we add a district
column to the table as below:
ALTER TABLE village ADD COLUMN district VARCHAR;
Now we can verify if the column has been added using the below statement:
SELECT * FROM village;
Output:
Example 2:
First, we create a table (say, cars
), with car_id
and car_name
columns using the below statement:
CREATE TABLE cars( car_id SERIAL PRIMARY KEY, car_name VARCHAR NOT NULL );
Now we add a model
column to the table as below:
ALTER TABLE cars ADD COLUMN model VARCHAR;
Now we can verify if the column has been added using the below statement:
SELECT * FROM cars;
Output: