How to ADD COLUMN in PostgreSQL

How do I add a column to an existing table in PostgreSQL?

ALTER TABLE … ADD COLUMN creates a new column in an existing PostgreSQL table without rewriting unaffected data.

Description

Why use ALTER TABLE ADD COLUMN?

Add new attributes to a live table without downtime, enabling feature releases and analytics enhancements.

What is the exact syntax?

Use ALTER TABLE table_name ADD [COLUMN] column_name data_type [constraint] [options]; to append a column. Omitting COLUMN is allowed.

Which data types can be added?

Any valid PostgreSQL type (text, numeric, jsonb, etc.). Choose defaults carefully to avoid table rewrites.

How to add a nullable column quickly?

ALTER TABLE Customers ADD COLUMN phone text; executes instantly because NULL needs no storage.

How to set a default value safely?

Phase it: add column nullable, backfill, then attach the DEFAULT to prevent full-table rewrite on large tables.

Example backfill pattern

ALTER TABLE Orders ADD COLUMN status text; UPDATE Orders SET status = 'pending'; ALTER TABLE Orders ALTER COLUMN status SET DEFAULT 'pending' NOT NULL;

Can I add multiple columns at once?

Yes. ALTER TABLE Products ADD COLUMN weight numeric, ADD COLUMN color text;

Practical ecommerce example

Add a delivered_at timestamp to Orders to track fulfillment.

ALTER TABLE Orders ADD COLUMN delivered_at timestamptz;

Verify the addition

\d+ Orders in psql or query information_schema.columns.

Best practices

1. Always qualify table names in multi-schema setups.
2. Use transactions to bundle schema and data changes.
3. Communicate changes via migration files and code reviews.

Common mistakes

Forgetting to backfill before NOT NULL – leads to immediate error. Backfill first.
Applying DEFAULT on huge tables – causes rewrite and locking. Use phased approach.

What if I need to roll back?

Run ALTER TABLE … DROP COLUMN inside the same transaction if still pending. Otherwise create a new migration to remove it.

Where is performance impacted?

Only when adding a NOT NULL column with a non-null default; PostgreSQL rewrites every row. Plan maintenance windows for large tables.

Security considerations

Grant ALTER privilege cautiously. Use role-based access to limit who can change schemas.

Why How to ADD COLUMN in PostgreSQL is important