PostgreSQL DDL

How to Add a Column in PostgreSQL (ALTER TABLE Examples)

Add columns to PostgreSQL tables with ALTER TABLE. Covers ADD COLUMN, defaults, IF NOT EXISTS, generated columns, and migration patterns.

Mar 12, 2026 4 min read

Introduction

PostgreSQL makes adding columns fast and safe. With volatile and non-volatile defaults handled differently, understanding the behavior helps you avoid downtime.

Add a Single Column

Basic column addition with optional default value.

-- Add column with default:
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) DEFAULT NULL;

-- Add NOT NULL column with default:
ALTER TABLE users
ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

Tip: Since PostgreSQL 11, adding a column with a constant DEFAULT is instant — no table rewrite. The default is stored in the catalog.

Add Column IF NOT EXISTS

Safely add a column without error if it already exists. Perfect for migrations.

-- Won't error if column exists:
ALTER TABLE users
ADD COLUMN IF NOT EXISTS phone VARCHAR(20);

-- Multiple safe additions:
ALTER TABLE users
ADD COLUMN IF NOT EXISTS phone VARCHAR(20),
ADD COLUMN IF NOT EXISTS avatar_url TEXT;

Tip: IF NOT EXISTS makes migrations idempotent — you can run them multiple times safely.

Add Generated (Computed) Column

PostgreSQL 12+ supports generated columns that auto-compute from other columns.

-- Stored generated column:
ALTER TABLE products
ADD COLUMN profit NUMERIC
GENERATED ALWAYS AS (price - cost) STORED;

-- Full name from first + last:
ALTER TABLE users
ADD COLUMN full_name TEXT
GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

Tip: STORED means the value is physically saved and updated automatically. PostgreSQL does not support VIRTUAL generated columns (yet).

Add Multiple Columns

Add several columns in a single statement.

ALTER TABLE orders
ADD COLUMN tracking_number VARCHAR(100),
ADD COLUMN shipped_at TIMESTAMPTZ,
ADD COLUMN carrier TEXT DEFAULT 'unknown';

Tip: Multiple column additions in one ALTER TABLE are transactional — either all succeed or none do.

Add Column with Constraint

Add columns with CHECK constraints, foreign keys, or unique constraints.

-- With CHECK constraint:
ALTER TABLE products
ADD COLUMN rating SMALLINT DEFAULT 0
CONSTRAINT chk_rating CHECK (rating BETWEEN 0 AND 5);

-- With foreign key:
ALTER TABLE orders
ADD COLUMN warehouse_id INTEGER
REFERENCES warehouses(id);

-- With unique constraint:
ALTER TABLE users
ADD COLUMN username TEXT UNIQUE;

Tip: Adding a foreign key validates all existing rows. On large tables, add the column first, then add the constraint using NOT VALID + VALIDATE separately.

Best Practices

Use IF NOT EXISTS for idempotent migrations
Constant defaults are instant (PG 11+) — no table rewrite
For large tables, add foreign keys with NOT VALID then VALIDATE CONSTRAINT separately
Use generated columns for computed values instead of triggers
Wrap DDL in transactions (PostgreSQL supports transactional DDL)

Generate PostgreSQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct PostgreSQL query instantly.

Try AI2SQL Free

No credit card required

Frequently Asked Questions

Does adding a column lock the table in PostgreSQL?

Adding a column with a constant DEFAULT is instant since PostgreSQL 11 — no lock, no rewrite. Adding with a volatile default (like NOW()) does rewrite the table.

Can I add a column at a specific position in PostgreSQL?

No. PostgreSQL always adds columns at the end. Column order cannot be changed without recreating the table. This is by design — column order doesn't affect performance.

Can AI2SQL generate PostgreSQL DDL?

Yes. Tell AI2SQL "add an email verification column to users" and it generates correct ALTER TABLE with appropriate constraints.