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.
Top comments (0)