Creating and altering schema with DDL

CREATE, ALTER and DROP, identity and generated columns, the safe way to change a busy table, temp tables, and which engines can roll DDL back.

Creating objects

DDL describes structure rather than data, but the same care applies: every statement you run against production will still be there in a year, so write it so a reader can understand the intent.

CREATE TABLE IF NOT EXISTS events (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind    TEXT NOT NULL,
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  day     DATE GENERATED ALWAYS AS (at::date) STORED
);

-- a table built from a query: handy for staging and scratch work
CREATE TABLE active_users AS
SELECT id, email FROM users WHERE last_seen_at > now() - interval '30 days';

-- a session-scoped table that disappears when the transaction ends
CREATE TEMP TABLE batch ON COMMIT DROP AS
SELECT * FROM staging_rows WHERE batch_id = 7;

Changing a table that is in use

The dangerous part of DDL is not the syntax, it is the lock. A statement that takes an exclusive lock on a table with a million rows and live traffic stops the application for as long as it runs.

-- cheap: a nullable column with no default
ALTER TABLE users ADD COLUMN locale TEXT;

-- still cheap on modern engines: a constant, NOT NULL default
ALTER TABLE users ADD COLUMN theme TEXT NOT NULL DEFAULT 'light';

-- a CHECK constraint validates every existing row, so split it
ALTER TABLE users ADD CONSTRAINT chk_email CHECK (email LIKE '%@%') NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email;

-- changing a type on a large table: do it in steps over several releases
ALTER TABLE users ADD COLUMN email_norm TEXT;
UPDATE users SET email_norm = lower(trim(email)) WHERE email_norm IS NULL;
ALTER TABLE users ALTER COLUMN email_norm SET NOT NULL;
-- once the application writes both, drop the old column much later

• Expand, then contract: add the new column, backfill in batches, move the application, and only then remove the old one.
• Adding a NOT NULL column to a table with rows needs a default, or the statement fails — there is no value to fill existing rows with.
• Renaming a column is a deploy, not a message: every caller breaks until all of them ship.
• For indexes on busy tables use the non-blocking path (CREATE INDEX CONCURRENTLY), and expect it to take longer than the locking version.

Temp tables and transactional DDL

BEGIN;
  CREATE TABLE audit_tmp (id BIGINT, note TEXT);
  INSERT INTO audit_tmp VALUES (1, 'test');
ROLLBACK;   -- PostgreSQL and SQLite remove the table; MySQL and Oracle keep it

CREATE TEMP TABLE scratch (n INT);   -- visible only to this session
DROP TABLE IF EXISTS audit_tmp CASCADE;   -- also drops dependent views

• A failed migration on a non-transactional engine leaves the schema half-applied, so every step must be safe to run twice.
• CASCADE on DROP removes dependent views and foreign keys; RESTRICT reports what would break instead of destroying it. Read the error before switching to cascade.
• Temp tables are per session, and each connection in a pool gets its own — never use them to pass state between requests.

FAQ

Related

Constraints, keys and normalisation Views, functions, procedures and triggers

Last refreshed 2026-09-18.