🕯️LORE

DDL - Tables & Schemas

Properties2
tagspostgresql, sql, ddl, create-table
aliasesCREATE TABLE, ALTER TABLE, DROP TABLE

4 min read

DDL - Tables & Schemas

Definition

DDL (Data Definition Language) statements define and modify the structure of the database itself - tables, columns, schemas - as opposed to DML, which manipulates the data inside them (see 05-DML-Insert-Update-Delete).

Schemas (Namespaces for Tables)

CREATE SCHEMA sales;
CREATE TABLE sales.orders (id SERIAL PRIMARY KEY, amount NUMERIC);
 
SET search_path TO sales, public;    -- tables can then be referenced without the schema prefix
DROP SCHEMA sales CASCADE;             -- CASCADE also drops everything inside it

Schemas organize large databases

Every database starts with a default public schema. Use additional schemas to logically separate concerns (e.g. sales, analytics, audit) within a single database, especially useful for multi-tenant or multi-team systems.

CREATE TABLE

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INT CHECK (age >= 0),
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE IF NOT EXISTS users (...);    -- avoids an error if the table already exists

GENERATED ... AS IDENTITY (Modern Auto-Increment)

id BIGINT GENERATED ALWAYS AS IDENTITY           -- can't be manually overridden on INSERT
id BIGINT GENERATED BY DEFAULT AS IDENTITY          -- CAN be manually overridden if explicitly provided

IDENTITY vs SERIAL

GENERATED ... AS IDENTITY is the SQL-standard replacement for the older Postgres-specific SERIAL type, offering clearer semantics around whether values can be manually inserted. Prefer IDENTITY in new schemas.

Generated (Computed) Columns

CREATE TABLE rectangles (
    width NUMERIC,
    height NUMERIC,
    area NUMERIC GENERATED ALWAYS AS (width * height) STORED    -- auto-computed, physically stored
);

CREATE TABLE ... AS (From a Query)

CREATE TABLE active_users AS
SELECT * FROM users WHERE is_active = true;

LIKE (Copy Structure from Another Table)

CREATE TABLE users_backup (LIKE users INCLUDING ALL);   -- copies columns, constraints, indexes, defaults

ALTER TABLE

ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users RENAME TO customers;
 
ALTER TABLE users ALTER COLUMN age SET DEFAULT 0;
ALTER TABLE users ALTER COLUMN age DROP DEFAULT;
ALTER TABLE users ALTER COLUMN age SET NOT NULL;
ALTER TABLE users ALTER COLUMN age DROP NOT NULL;
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;              -- change a column's type
ALTER TABLE users ALTER COLUMN price TYPE NUMERIC(10,2) USING price::NUMERIC(10,2);  -- with an explicit cast
 
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
ALTER TABLE users DROP CONSTRAINT unique_email;

ALTER TABLE ... TYPE can lock the table and rewrite every row

On large tables, changing a column’s type (especially with a USING cast) can take a long time and hold an exclusive lock, blocking reads/writes. Test on a copy first, and consider running during low-traffic windows for big tables.

DROP TABLE

DROP TABLE users;
DROP TABLE IF EXISTS users;
DROP TABLE users CASCADE;     -- also drops dependent objects (views, foreign keys referencing it)

DROP TABLE is irreversible outside of a transaction

Wrap risky DDL in a transaction while testing (BEGIN; DROP TABLE ...; ROLLBACK;) - Postgres, unlike many databases, supports transactional DDL, so you can safely undo a DROP TABLE if it’s still inside an uncommitted transaction. See 12-Transactions.

TRUNCATE (Fast Delete-All)

TRUNCATE TABLE users;                       -- removes ALL rows, resets identity sequences
TRUNCATE TABLE users RESTART IDENTITY;        -- explicitly reset auto-increment counters
TRUNCATE TABLE users CASCADE;                   -- also truncate tables with foreign keys pointing here

TRUNCATE vs DELETE FROM table (no WHERE)

TRUNCATE is much faster on large tables because it deallocates whole data pages instead of logging each row deletion individually, but it can’t be filtered with WHERE and resets identity sequences by default.

Table Inheritance & Partitioning (Advanced)

-- Declarative partitioning by range (e.g. for time-series data)
CREATE TABLE events (
    id BIGINT,
    event_time TIMESTAMPTZ NOT NULL,
    payload JSONB
) PARTITION BY RANGE (event_time);
 
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

Partitioning at scale

Splitting a huge table into partitions (by date range, list, or hash) lets Postgres skip scanning irrelevant partitions entirely for filtered queries, and makes bulk deletes (e.g. “drop last year’s data”) near-instant via DROP TABLE on the old partition instead of a slow DELETE.

Inspecting Table Structure

\d users              -- psql meta-command, full column/constraint/index details
\d+ users                -- includes storage size and comments
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users';

Graph View

Backlinks