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 modelcolumn 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: