Default Values & Generated Columns

~14 min · schema, defaults

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Auto-fill that you can trust

Defaults are the database's auto-fill. When an INSERT skips a column, the default kicks in. This keeps inserts terse, keeps data consistent, and removes a category of "we forgot to set X" bugs.

Static, expression, and generated

Three flavours, in increasing power:

Static defaults: literal values like 0, 'draft', FALSE.
Expression defaults: computed at INSERT time — now(), CURRENT_DATE, gen_random_uuid(), encode(gen_random_bytes(32),'hex').
Generated columns: computed from other columns in the same row — STORED (written to disk) or VIRTUAL (computed on read; PG 18+).

Generated columns earn their keep

A generated column derives its value from a formula. It can never be inserted or updated directly — the database keeps it correct. Use them for derived values that you'd otherwise compute in the application a thousand times: full names, totals with tax, slugified titles, normalised search terms.

Code

Static and expression defaults·sql

CREATE TABLE posts (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title      TEXT NOT NULL,
    status     TEXT NOT NULL DEFAULT 'draft',
    views      INTEGER NOT NULL DEFAULT 0,
    slug       TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Only title is required; everything else fills itself in.
INSERT INTO posts (title) VALUES ('My First Post');

Expression default for an API key·sql

CREATE TABLE api_keys (
    id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    secret  TEXT NOT NULL DEFAULT encode(gen_random_bytes(32), 'hex'),
    created TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Each row gets a 64-char hex secret — generated server-side, application-free.
INSERT INTO api_keys (user_id) VALUES (1);

STORED and VIRTUAL generated columns (PG 18)·sql

CREATE TABLE products (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    price      NUMERIC(10,2) NOT NULL,
    tax_rate   NUMERIC(4,3) NOT NULL DEFAULT 0.10,
    -- STORED: computed on write, persisted; can be indexed.
    total      NUMERIC GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED,
    -- VIRTUAL: computed on read, takes no storage; PG 18+.
    margin_pct NUMERIC GENERATED ALWAYS AS ((price - 10) / NULLIF(price,0) * 100) VIRTUAL
);

External links

PostgreSQL Generated Columns

Exercise

Add a STORED generated column to a table you control. Confirm that direct INSERT/UPDATE on that column fails with a clear error. If you're on PG 18+, add a VIRTUAL column too and notice the storage difference.