How to add new columns to a table in PostgreSQL

Add new columns to a table in PostgreSQL

We can combine ALTER TABLE and ADD COLUMN to add new columns to an existing table.

ALTER TABLE table_name 
ADD COLUMN  column_name1 data_type, 
ADD COLUMN  column_name1 data_type, 
Constraints;

Let’s say we have a user table with the elements name and age. Now we need to add a new column phone_number.

alter table user 
add column phone_number bigint;

Adding multiple columns

Consider if we needed to add two more columns, salary and nickname.

ALTER TABLE user 
ADD COLUMN salary int, 
ADD COLUMN nickname varchar;

Add Column if not present

With Postgres 9.6, this can be done using the option if not exists.

# syntax
ALTER TABLE table_name 
ADD COLUMN IF NOT EXISTS column_name data_type;

Adding with constraints

We can add constraints like we add default values, which are not null in the new column.

#syntax
ALTER TABLE table_name
ADD COLUMN column_name datatype
constraints;

Let’s say we need to add a new column with a default value in that column.

Alter table user 
add column isAlive boolean default true;

To add a NOT NULL constraint, you need to set a default value, because when you add a new column, PostgreSQL takes NULL as the column value for the existing row, which violates the NOT NULL constraint.

Alter table user 
add column isAlive boolean not null default true;

---

If you don’t want the default value:

• Add a column with the default value constraint

Alter table user 
add column isAlive boolean not null default true;

• Then, remove the default value constraint

ALTER TABLE user 
ALTER COLUMN isAlive 
DROP DEFAULT;

---