Add new columns to table in PostgreSQL

Adding columns

We can combine ALTER TABLE & 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 table user , with 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 we need 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

ALTER TABLE table_name
ADD COLUMN IF NOT EXISTS column_name data_type;

Adding with constraints

ALTER TABLE table_name
ADD COLUMN column_name datatype
constraints;

Let's say we need to add a default value to a column.

Alter table user
add column isAlive boolean default true;

To add a not null constraint you need to set default value because, When you add new column PostgreSQL takes the NULL as 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

• Then remove the default value constraint

ALTER TABLE user
ALTER COLUMN isAlive
DROP DEFAULT;

Thanks for reading.