ALTER TABLE in PostgreSQL

The ALTER TABLE statement is the primary way to modify the structure of an existing PostgreSQL table. It covers a wide range of operations: adding or removing columns, changing column types and defaults, enforcing or removing constraints, and renaming the table. Understanding which operations require a table rewrite or an exclusive lock is critical for production deployments.

Syntax

-- Add a column
ALTER TABLE table_name ADD COLUMN column_name datatype [constraint];

-- Drop a column
ALTER TABLE table_name DROP COLUMN column_name [CASCADE];

-- Rename a column
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- Change column default
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT value;
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

-- Change NOT NULL
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

-- Add a named constraint
ALTER TABLE table_name ADD CONSTRAINT name constraint_definition;

-- Drop a constraint
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

-- Rename the table
ALTER TABLE table_name RENAME TO new_table_name;

Practical Example

Start with an events table and walk through common ALTER TABLE operations:

CREATE TABLE events (
  event_id  SERIAL PRIMARY KEY,
  title     VARCHAR(300) NOT NULL,
  venue     VARCHAR(200) NOT NULL
);

Add a nullable column (metadata-only — instant even on large tables):

ALTER TABLE events ADD COLUMN published BOOLEAN;

Add multiple columns in one statement (one lock acquisition):

ALTER TABLE events
  ADD COLUMN starts_at  TIMESTAMPTZ,
  ADD COLUMN ends_at    TIMESTAMPTZ,
  ADD COLUMN ticket_price NUMERIC(10,2) NOT NULL DEFAULT 0;

Set a default on an existing column:

ALTER TABLE events ALTER COLUMN published SET DEFAULT FALSE;

Add a NOT NULL constraint to a column that already has data — use the three-step pattern:

-- Step 1: backfill nulls
UPDATE events SET published = FALSE WHERE published IS NULL;

-- Step 2: enforce NOT NULL
ALTER TABLE events ALTER COLUMN published SET NOT NULL;

Add a named CHECK constraint:

ALTER TABLE events
  ADD CONSTRAINT chk_event_dates CHECK (ends_at > starts_at);

Find constraint names, then drop one:

\d events

ALTER TABLE events DROP CONSTRAINT chk_event_dates;

Rename a column and then the table:

ALTER TABLE events RENAME COLUMN venue TO venue_name;
ALTER TABLE events RENAME TO scheduled_events;

Inspect the final structure:

\d scheduled_events

Operation Impact Reference

Testing with Vela

Schema changes that require a table rewrite — like changing a column’s type or adding NOT NULL to a large table — can cause prolonged locks. Vela database branching lets you test these migrations on a production-data clone first: measure how long the operation takes, confirm the USING expression produces correct values, and rehearse the rollback plan before running against the live system.

Production Tips

• Adding a nullable column is safe and instant in PostgreSQL — it does not rewrite the table or block reads.
• 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 named constraints (ADD CONSTRAINT name ...) so you can drop them easily later by name.
• Use ALTER TABLE IF EXISTS in deployment scripts to handle cases where a migration was partially applied.
• For zero-downtime type changes on large tables, consider the add+backfill+switch+drop pattern rather than ALTER COLUMN ... TYPE.