How to Add Columns to a Table in PostgreSQL

In this tutorial, we will be learning to add single or multiple columns in PostgreSQL. This is going to be a very simple tutorial where we will use the ALTER TABLE statement with the ADD COLUMN statement. So, let’s get started!

ADD COLUMN Statement in PostgreSQL

In PostgreSQL, the ADD COLUMN statement is used with the ALTER TABLE statement to add a new column. It is not a standalone statement.

Using the ADD COLUMN statement, we can add multiple columns in a single query which is a great thing for us. It is straightforward to add a new column using this command. Let’s see its syntax.

Syntax:

Following is the syntax to add a single column to the table.

-- add a single column
ALTER TABLE IF EXISTS tableName
ADD COLUMN colName dataType [ constraint ];

-- add multiple columns
ALTER TABLE IF EXISTS tableName
ADD COLUMN colName1 dataType [ constraint ],
ADD COLUMN colName2 dataType [ constraint ],
ADD COLUMN colNamen dataType [ constraint ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

• ALTER TABLE is used to modify the table structure so that we can add a new column,
• IF EXISTS clause is used to skip the error if the table name we specified doesn’t exist,
• tableName is the table name that you want to add the column in,
• ADD COLUMN is the must keyword to add a new column,
• colName dataType is any valid column name with the correct data type that you want to add,
• constraint is any following constraint that you want to apply on the column:

[ CONSTRAINT constraint_name ]
{
    NOT NULL |
    NULL |
    CHECK ( expression ) [ NO INHERIT ] |
    DEFAULT default_expr |
    GENERATED ALWAYS AS ( generation_expr ) STORED |
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
    UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
    PRIMARY KEY index_parameters |
    REFERENCES reftable [ ( refcolumn ) ]
        [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
        [ ON DELETE referential_action ]
        [ ON UPDATE referential_action ]
}
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Note that, most of the above constraints are not used frequently. If you are in the learning stage, I would suggest you look at NOT NULL, NULL, PRIMARY KEY, CHECK, DEFAULT and UNIQUE.

PostgreSQL ADD COLUMN Statement Examples

To demonstrate the ADD COLUMN statement, let’s create a table first.

Check: How to Create a Table in PostgreSQL?

Add a Single Column

We will create a table called users_post.

CREATE TABLE user_posts (
  post_id SERIAL PRIMARY KEY,
  post_content TEXT,
  post_date TIMESTAMP
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have successfully created the table. However, there is one very important field missing, guess which? It is the user name. So, let’s add it using the ADD COLUMN statement.

ALTER TABLE user_posts
ADD COLUMN username VARCHAR(100);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

When we execute the above query, it will create a new column. You can check the table structure using \d tableName command.

As you can see in the output above, our new column is added successfully.

Add a Column with a Reference

Now, let’s consider we have a parent table created already and we want to add a new column on another table that is referenced to the first table. In simple words, we have to create a relationship using the foreign key.

So, how can we add a foreign key with the new column? Let’s see.

In the above example, we have created the username column. Now, instead of keeping the column ‘username’, we will create a parent table called ‘users’ and add a new column ‘userId’ to the user_posts table after removing the column ‘username’.

-- create users table
DROP  TABLE IF EXISTS users;
CREATE TABLE  users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

-- drop username column
ALTER TABLE IF EXISTS user_posts
DROP COLUMN username;

-- create userid column on user_posts with the reference to users table
ALTER TABLE IF EXISTS user_posts
ADD COLUMN user_id INT  REFERENCES users(user_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In the above queries, we are first creating the users table. Then we are deleting the username column from the user_posts table and finally add a new column ‘user_id’ with a reference to the ‘users’ table.

Let’s check the user_posts table and see if the reference has been created or not.

As you can see, the new column has been added successfully with the correct reference.

Add Column with NOT NULL Constraint

You may already know about the NOT NULL constraint. It is the constraint to the column which makes sure the column must get some value via the INSERT command. Now, if you try to add a new column to the table which already has some data with the NOT NULL constraint, you will get an error saying your table contains null values. Look below-

In short, if your table has some data and you are trying to add a new column with the NOT NULL constraint, it makes the column empty, resulting in an error.

To avoid this error, there is a workaround for us. Let’s see.

The workaround is, create a column first with the DEFAULT constraint and not the NOT NULL constraint. The DEFAULT constraint will insert the default value in the column for all records. Now we have values for all records in the column and we can remove the DEFAULT constraint and add the NOT NULL constraint.

It’s quite simple, let’s make it happen!

 -- add column with DEFAULT constraint
ALTER TABLE user_posts
ADD COLUMN views INT DEFAULT 0;

-- remove DEFAULT constraint and add not null
ALTER TABLE user_posts 
ALTER COLUMN views DROP DEFAULT;

-- add NOT NULL constraint
ALTER TABLE user_posts
ALTER COLUMN views SET NOT NULL; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the first query adds a new column with the default value. The second query removes the DEFAULT constraint and the third query again adds the NOT NULL constraint.

Let’s check the table structure to see if the NOT NULL constraint is added to the column.

As you can see, the NOT NULL constraint has been added successfully.

Conclusion

In this tutorial, we have learned to add new columns with and without constraints. The topics we covered in this tutorial are very important and you might have spent a lot of time resolving the errors mentioned in this tutorial if you haven’t read this tutorial. I hope you have learned something new in this tutorial. Don’t forget to go through the official documentation of PostgreSQL as well so that you will get thorough knowledge about this topic.

Reference

PostgreSQL official documentation