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:

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.

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

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

<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/word-image-509.png" data-lazy- height="342" src="data:image/svg xml,” width=”1506″>

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

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.

<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/word-image-510.png" data-lazy- height="385" src="data:image/svg xml,” width=”738″>

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.

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.

<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/word-image-511.png" data-lazy- height="355" src="data:image/svg xml,” width=”831″>

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.

<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/word-image-512.png" data-lazy- height="311" src="data:image/svg xml,” width=”815″>

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:

In this case, we will get an 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.