Add Column PostgreSQL

After creating databases and populating them with information, you will rarely need to alter the database structure. The recurrent thing you will do is to add or retrieve records stored in the database. However, there are rare instances where you may need to add a new column and populate it with data.

In this guide, we will look at how to alter a PostgreSQL table and add a column.

Basic Usage

To add a column to a table, we first need to specify the “alter query” followed by the table name.

We can express the general syntax to add a column as:

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name DATA_TYPE

In the syntax represented above, we start by calling the ALTER TABLE query followed by the name of the table to which we want to add the column. Next, specify the column name after the ADD COLUMN statement.

Although the IF NOT EXISTS statement is optional, it can be a helpful way to avoid errors if a column with a similar name exists.

Finally, we specify the data type of the column and the column constraints.

You can also add multiple columns by specifying their name, data type, and constraints one after another (separated by commas).

NOTE: PostgreSQL adds any new column to the end of the table as it does not have a method of specifying column position. Hence, to create a new column layout in a PostgreSQL table, you have to recreate the new table or use a view.

Examples

Let’s add a column to the city table in the sakila database.

ALTER TABLE city ADD COLUMN city_code VARCHAR(20);

The above query appends the column city_code to the city table. You can verify by using the select statement as:

SELECT * from city LIMIT 5;

As shown in the screenshot below, the above query should return the records in the city table with the new column:

To add multiple columns simultaneously, specify the column values in a list as:

ALTER TABLE city

ADD COLUMN IF NOT EXISTS population SERIAL NOT NULL,

ADD COLUMN IF NOT EXISTS streets VARCHAR(255),

ADD COLUMN other INT;

The above query should add three columns sequentially as specified. The order of appending the columns to the table is as specified.

For example, in the above query, the population column will come before the streets.

In the previous examples, all the columns added contains NULL values. To specify a placeholder value, we can use the DEFAULT keyword.

Consider the city code column. Let us start by dropping the column.

ALTER TABLE city DROP COLUMN city_code;

Next, append the column with the ADD COLUMN keyword; the default value is in the query below:

NOTE: The city_code column will be at the end of the table.

ALTERTABLE city ADDCOLUMNcity_codeVARCHAR(20) DEFAULT 'AA';

In the above example, PostgreSQL will use the specified default value to populate the values of the city_code column.

Using the specified default value can be useful when the column has a NOT NULL constraint.

Adding a column can take any supported PostgreSQL data type. For example, the following contains a column of Boolean values.

ALTER TABLE city ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT true;

NOTE: Since PostgreSQL will add NULL values to a column if no default values are specified, it is good to add a default value. If not, PostgreSQL will return an error!

For example, we can create a table as:

ALTER TABLE city DROP COLUMN no_null

ALTER TABLE city ADD COLUMN IF NOT EXISTS no_null BOOLEAN NOT NULL;

In this case, we will get an SQL State 23502.

ERROR: column "no_null" of relation "city" contains null values
SQL state: 23502

SQL State 23502 indicates that the operation (UPDATE, SET OR INSERT is NULL but object does not accept NULL values).

Conclusion

This tutorial has walked you through how to to use and work with the ADD COLUMN query in PostgreSQL, allowing you to add columns to an existing table.

Add Column PostgreSQL

Basic Usage

Examples

Conclusion

About the author

John Otieno