PostgreSQL Add Columns

In this article, you will learn how to add one or more columns to a table using PostgreSQL ALTER TABLE ... ADD COLUMN statements.

Sometimes you need to add a new column to an existing table to hold some necessary information.

Suppose, you have a user table that stores username, email, password, etc. But as the needs of the system change, you need to store the mobile phone number in the user table. To do this, you don’t need to recreate a table, just add a column to the existing table.

PostgreSQL allows you to use the ALTER TABLE statement to modify an existing table. To add one or more columns to a table, use the ALTER TABLE ... ADD COLUMN statement.

PostgreSQL ADD COLUMN syntax

To add a new column to an existing table, use the ALTER TABLE ... ADD COLUMN statement as as follows:

ALTER TABLE table_name
ADD [COLUMN] [IF NOT EXISTS] column_name data_type column_contraint
[, ADD [COLUMN] ...];

Explanation:

  • The table_name is the table to add the column to.

  • The ADD [COLUMN] ... clause is used to add a column. The COLUMN keyword can be omitted. If you want to add multiple columns in one statement, use multiple comma-separated ADD [COLUMN] ... clauses.

  • The column_name is the name of the column to add. Column names should conform to the following rules:

    • Column names can consist of letters, numbers, underscores, and dollar signs, with a maximum length of 63 characters.
    • Column names are unique within a table.

  • The data_type is the data type of the column to be added, such as: VARCHAR, INTEGER, BOOLEAN, DATE, TIME, TIMESTAMP, ARRAY, JSON etc.

  • The column_contraint is the constraint on the column to be added, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK etc.

  • The IF NOT EXISTS can avoid errors caused by giving duplicate column names. It is optional.

The new column will be added to the end of the table. You cannot specify a position for the new column.

If there are already some rows in the table, the constraint of the new column may cause an error, you can add a default value on the column definition, or solve it with the following steps:

  1. Add the columns without constraints.
  2. Update the data of the newly added column.
  3. Add constraints to the new columns.

Show all columns in the table

Before adding a column to a table, you may need to determine if a column with the same name exists in the table.

To view all columns of a table in PostgreSQL, you can use the \d command to display the definition of the table, or to get columns from the information_schema.columns table.

The usage of the \d command is as follows:

\d table_name

To find all columns of a table in the information_schema.columns table, use the following statement:

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'table_name';

PostgreSQL ADD COLUMN Examples

This example demonstrates how to use the ALTER TABLE ... ADD COLUMN statement to add one or two columns to a table.

Suppose, there is a user table with two columns: id and name.

Create a table named users in the testdb database to store user information using the following statement :

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

If you do not have a testdb database, use the following statement create database first and connect to the database:

CREATE DATABASE testdb;
\c testdb;

After the table is created, you can show all the columns in this table with the \d command:

\d users;

                                   Table "public.users"
Column |          Type          | Collation | Nullable |              Default
--------+------------------------+-----------+----------+-----------------------------------
id     | integer                |           | not null | nextval('users_id_seq'::regclass)
name   | character varying(100) |           | not null |
Indexes:
   "users_pkey" PRIMARY KEY, btree (id)

Insert a row into the user table, as follows;

INSERT INTO users (name) values ('Tim');

Add a column to a table using PostgreSQL ADD COLUMN

To store the user’s age in the users table, you need to add a column named age using the following statement,

ALTER TABLE users
ADD COLUMN age INTEGER NOT NULL;

ERROR:  column "age" contains null values

Here, PostgreSQL gives an error. This is because the table is not an empty table, it already has a row. The NOT NULL constraint of the age column caused this error. To avoid this error, you can specify a default value for the age column as follows:

ALTER TABLE users
ADD COLUMN age INTEGER NOT NULL DEFAULT 18;

Here, we’ve added a age column of type INTEGER, and it’s a non-null column with a default value of 18.

After adding the age column, the values ​​of the age column of all the original row are 18. The following statement retrieves all the rows in the users table:

SELECT * FROM users;

id | name | age
----+------+-----
 1 | Tim  |  18
(1 row)

Add two columns to a table using PostgreSQL ADD COLUMN

As your system evolves, you may need to save users’ email and phone number information in the user table. This is almost a must-have information for users.

To add email and cellphone columns to the users table, use the following statement:

ALTER TABLE users
ADD COLUMN email VARCHAR(100),
ADD COLUMN cellphone VARCHAR(100);

Here, we’ve added two columns users to the table: email for email addresses and cellphone for mobile numbers.

Let’s examine the rows of the users table:

SELECT * FROM users;

id | name | age | email | cellphone
----+------+-----+-------+-----------
 1 | Tim  |  18 |       |
(1 row)

Finally, let’s view all the columns in this table using the \d command:

\d users;

                                    Table "public.users"
 Column   |          Type          | Collation | Nullable |              Default
-----------+------------------------+-----------+----------+-----------------------------------
id        | integer                |           | not null | nextval('users_id_seq'::regclass)
name      | character varying(100) |           | not null |
age       | integer                |           | not null | 18
email     | character varying(100) |           |          |
cellphone | character varying(100) |           |          |
Indexes:
   "users_pkey" PRIMARY KEY, btree (id)

Conclusion

PostgreSQL provides the ALTER TABLE ... ADD COLUMN statement to add one or more columns to a table. With the ALTER TABLE statement, you can also rename tables, rename columns, drop columns, modify column properties, and more.