Proper way to ADD COLUMN in PostgreSQL

The majority of the time, software needs to be changed, requiring us to later add a column to an existing table through modification. In the instances below, we need to ADD COLUMN to an existing PostgreSQL table.

1. If the data has already imported into the table.
2. Foreign key constraints are added to the current table by another table.

A new column can be added to an existing table using the ADD COLUMN command in PostgreSQL.

ADD COLUMN Syntax

The syntax for adding a new column to an already existing PostgreSQL database table is shown below.

ALTER TABLE <table_name>
ADD COLUMN <column_name> DATATYPE;

Put the name of the table you wish to change in place of <table_name>.

Put the new column name in place of <column_name>.

In the data type, you can specify all supported PostgreSQL data types.

PostgreSQL ADD COLUMN Examples

ALTER TABLE Employee
ADD COLUMN lastname VARCHAR(30);

In the example above, I have added a new column “lastname” of data type varchar of length 30 to a existing table Employee.

In another example, I have added “age” column of datatype INT to an existing PostgreSQL table.

ALTER TABLE Employee
ADD COLUMN age INT;

The new column added by the ADD COLUMN command will enter NULL values into the table if it already contains data. The screenshot below shows the addition of a “lastname” and “age” column to the Employee table with NULL values.

ADD COLUMN WITH DEFAULT VALUES

PostgreSQL’s NULL behavior can be worked around by adding a column with default values. This prevents NULL values from being inserted and assigns a default value to every row in table that already exists.

Below the syntax of PostgreSQL ADD COLUMN command with Default Value.

ALTER TABLE <table_name>
ADD COLUMN <column_name> <data_type> DEFAULT <default_value>;

We can specify <default_value>, which is compatible with the data type after DEFAULT. For instance, we can only specify a string values if we define a data type of VARCHAR, and we can only specify integer values if we write datatype of INT.

Below are the examples.

ALTER TABLE Employee
ADD COLUMN department VARCHAR(30) DEFAULT 'IT';

ALTER TABLE Employee
ADD COLUMN age int DEFAULT 30;

In the above examples, I have added department column with default value of ‘IT’ and added a age column with default value of 30. All of the current rows will now have the default values for their relevant columns as shown below.

Timestamp default value

We can use the command below to set the timestamp’s default value.

ALTER TABLE Employee
ADD COLUMN entry_time timestamp DEFAULT now();

To set the default time with time zone, use below command.

ALTER TABLE Employee
ADD COLUMN entry_time timestamp with time zone DEFAULT now();

ADD COLUMN – Different default values for existing and new rows

In certain cases, we must specify a distinct value for newly created rows in addition to a different value rows that already exists.

This situation can arise, for instance, when you want to label fresh data with a new value and older data with an old value.

ALTER TABLE Employee
ADD COLUMN status VARCHAR(30) DEFAULT 'old',
ALTER COLUMN status SET default 'new';

In the above screenshot, we have added one column status with “old” value for existing rows. when we insert new row into the table the status column got ‘new’ as the default value as shown in below screenshot.

ADD COLUMN WITH NOT NULL

When you try to add a new column with NOT NULL, then PostgreSQL will throw an error like shown below.

To resolve the problem, we have two options.

1. Set the default value in ADD COLUMN command.
2. Add column with NULL, then update all the existing records, and then modify the column to set NOT NULL.

Option 1

In the first option, I have added a default value with add column command as shown below.

ALTER TABLE Employee
ADD COLUMN dob DATE DEFAULT now();

Option 2

In option 2, I have first add column with NULL as shown below:

ALTER TABLE Employee
ADD COLUMN dob DATE NULL;

Then update all existing rows with some value.

UPDATE Employee  SET dob = CURRENT_DATE - 1;

Then set column to default NOT NULL value.

ALTER TABLE Employee
ALTER COLUMN dob SET NOT NULL;

ADD MULTIPLE COLUMNS with single PostgreSQL command

You can add multiple columns within a single command. Below is the example.

ALTER TABLE Employee
ADD COLUMN address1 VARCHAR(30),
ADD COLUMN address2 VARCHAR(30),
ADD COLUMN address3 VARCHAR(30);

You need to add comma at the end of each ADD COLUMN command except last line.

Read more about the ALTER TABLE and ADD COLUMN commands by reading PostgreSQL documentation.

Summary

If you found this tutorial helpful, please share your suggestions and feedback in the comments below.