PostgreSQL ALTER TABLE ADD COLUMN: Syntax and Examples

Use ALTER TABLE ... ADD COLUMN to add a column to an existing PostgreSQL table:

ALTER TABLE users
ADD COLUMN phone text;

If the table already has rows and no default is specified, the new column is NULL for those rows.

ADD COLUMN syntax

ALTER TABLE table_name
ADD [COLUMN] [IF NOT EXISTS] column_name data_type
    [COLLATE collation] [column_constraint ...];

COLUMN is optional. A column constraint can include DEFAULT, NOT NULL, CHECK, UNIQUE, PRIMARY KEY, or REFERENCES. Use a PostgreSQL data type such as text, integer, boolean, date, or jsonb.

Add a column with a default

When you add a column with a default, existing rows receive the value of that default and future inserts use it when they omit the column:

ALTER TABLE users
ADD COLUMN status text NOT NULL DEFAULT 'active';

The default must satisfy any constraints on the new column. Without a default, adding a NOT NULL column to a nonempty table fails because the existing rows would have null values. Choose a suitable default or add the column as nullable, backfill it, and then set NOT NULL:

ALTER TABLE users ADD COLUMN normalized_email text;

UPDATE users
SET normalized_email = lower(email)
WHERE normalized_email IS NULL;

ALTER TABLE users ALTER COLUMN normalized_email SET NOT NULL;

Changing the default later affects subsequent inserts (or updates that explicitly use SET status = DEFAULT); it does not replace values already stored in existing rows:

ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'new';

In current PostgreSQL, a constant default can be stored in table metadata without immediately rewriting every row. A volatile default such as clock_timestamp() must be evaluated for each existing row and can require a table rewrite. ALTER TABLE also generally takes an ACCESS EXCLUSIVE lock, so plan potentially long changes for a suitable maintenance window.

Add a column only if it does not exist

IF NOT EXISTS suppresses the duplicate-column error:

ALTER TABLE users
ADD COLUMN IF NOT EXISTS phone text;

If a column with that name already exists, PostgreSQL issues a notice and does nothing. It does not compare or update the existing column’s type, default, or constraints; inspect the schema before assuming the definitions match.

Add multiple columns

Separate ADD COLUMN actions with commas to add multiple columns in one statement:

ALTER TABLE users
    ADD COLUMN birth_date date,
    ADD COLUMN verified boolean NOT NULL DEFAULT false;

Inspect table columns

In psql, show the table definition with:

\d users

To query column names and types using SQL:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = current_schema()
  AND table_name = 'users'
ORDER BY ordinal_position;