Add Columns to a Table in PostgreSQL

Use ALTER TABLE ADD COLUMN statement to add a new column to existing table. Postgres does not support adding multiple columns by one ALTER TABLE statement. Hence, if you want to add multiple columns to a table, you need to execute ALTER TABLE command multiple times.

Syntax:

ALTER TABLE [schema_name.]table_name 
ADD COLUMN <column_name> <data_type> [column_constraint];

The column_constraint is optional. You can add new columns to a table with or without adding constraint.

Consider that you already have the following employee table.

Let's add a new 'salary' column into the employee table.

Example: Add NOT NULL Column

ALTER TABLE employee
ADD COLUMN salary INT NOT NULL;

Now, the employee table will have a new salary column, as shown below.

If a table already has data, then a new column cannot be added with NOT NULL constraint. It will raise error that column contains null values. To solve this, you first need to create a nullable column, then insert data in a column, and then add a NOT NULL column constraint.

The follwing will add a nullable column 'salary'.

Example: Add Nullable Column

ALTER TABLE employee
ADD COLUMN salary INT;

Add Column using pgAdmin

You can also use pgAdmin to add columns into a table. Expand Tables node in the left pane and right click on the table name or columns node and select Create -> Column.. in the content menu.

This will open Create Column popup where you can enter column name, data type, constraints, etc.

Click on Save button to add a column into a table.

Alternatively, you can add one or more columns in pgAdmin by right clicking on the the table name and select 'Properties'. In the popup, go to 'Columns' tab wherein you can add multiple columns by clicking on the + symbol and enter column name, data type, length, NOT NULL at the bottom row.