Add Column in PostgreSQL

ALTER TABLE ... ADD COLUMN extends an existing PostgreSQL table with one or more new columns. New columns are always appended at the end of the column list. Adding a nullable column (or a column with a constant default in PostgreSQL 11+) is a metadata-only operation that completes instantly even on very large tables.

Syntax

ALTER TABLE table_name
  ADD COLUMN column_name data_type [constraint];

Add multiple columns in one statement to minimize lock acquisitions:

ALTER TABLE table_name
  ADD COLUMN col1 data_type constraint,
  ADD COLUMN col2 data_type constraint;

Practical Example

Start with a minimal products table and extend it over time:

CREATE TABLE products (
  product_id  SERIAL PRIMARY KEY,
  sku         VARCHAR(50) NOT NULL UNIQUE,
  name        VARCHAR(200) NOT NULL
);

Add a single nullable column — instant, no table rewrite:

ALTER TABLE products ADD COLUMN description TEXT;

Add multiple columns at once:

ALTER TABLE products
  ADD COLUMN unit_price   NUMERIC(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN is_active    BOOLEAN       NOT NULL DEFAULT TRUE,
  ADD COLUMN created_at   TIMESTAMPTZ   NOT NULL DEFAULT now();

Verify the updated structure:

\d products

Add a NOT NULL column to a table that already has rows — three-step pattern:

-- Step 1: add without NOT NULL (existing rows get NULL)
ALTER TABLE products ADD COLUMN supplier_code VARCHAR(20);

-- Step 2: populate existing rows
UPDATE products
SET supplier_code = 'UNKNOWN'
WHERE supplier_code IS NULL;

-- Step 3: enforce NOT NULL
ALTER TABLE products ALTER COLUMN supplier_code SET NOT NULL;

Confirm the constraint is in place:

SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'products'
  AND column_name = 'supplier_code';

Performance Characteristics

Testing with Vela

When adding columns to large, heavily trafficked tables, Vela database branching lets you test each migration step on a production-sized clone: measure lock duration, verify the backfill UPDATE, and confirm the SET NOT NULL validation pass before running against the live database.

Production Tips

• Adding a nullable column is safe and instant — it does not rewrite the table or block reads beyond the brief lock acquisition.
• Adding a column with a constant (non-volatile) default is also metadata-only in PostgreSQL 11+ and does not require a table rewrite.
• Never add a NOT NULL column directly to a non-empty table without a default — follow the three-step add, backfill, constrain pattern.
• Use a single ALTER TABLE with multiple ADD COLUMN clauses to minimize lock acquisitions when adding several columns at once.
• PostgreSQL appends new columns at the end of the column list. Control display order in SELECT statements rather than relying on physical column order.