SQL Academy · Lesson

DEFAULT Values and Generated Columns

Provide DEFAULTs (NOW(), uuid_generate_v4(), 'pending') and compute values on the fly with GENERATED ALWAYS AS columns.

DEFAULT Values and Generated Columns is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

DEFAULT: Auto-Fill Missing Values

When INSERT omits a column, the DEFAULT is used:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Common DEFAULTs

Patterns you'll use everywhere:

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
is_active BOOLEAN NOT NULL DEFAULT true
status TEXT NOT NULL DEFAULT 'pending'

Adding a DEFAULT Later

Existing rows aren't backfilled by default — only new inserts use the default:

ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- For existing rows, backfill manually:
UPDATE users SET status = 'active' WHERE status IS NULL;

Adding NOT NULL with DEFAULT

PostgreSQL 11+ supports fast adding of NOT NULL columns with a constant DEFAULT — it doesn't rewrite the table:

ALTER TABLE users
  ADD COLUMN signup_source TEXT NOT NULL DEFAULT 'web';
-- Fast in PG 11+, slow before

Computed Generated Columns (STORED)

A column whose value is automatically computed from other columns:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  qty INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL,
  total NUMERIC(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED
);

INSERT INTO orders (qty, unit_price) VALUES (3, 9.99);
SELECT * FROM orders;       -- total is auto-filled

Generated tsvector for Full-Text Search

A great use of STORED generated columns:

ALTER TABLE articles
  ADD COLUMN search_doc tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX articles_search_idx ON articles USING GIN(search_doc);

Virtual Generated Columns

PostgreSQL only supports STORED today (virtual coming in future versions). Other DBs (MySQL) support VIRTUAL columns that compute at SELECT time.

You Can't INSERT Into Generated Columns

Trying to write to one is an error — they're computed, not stored by you:

INSERT INTO orders (qty, unit_price, total) VALUES (3, 9.99, 30);
-- ERROR: column "total" can only be updated to DEFAULT

Sequence-Based Defaults

SERIAL/BIGSERIAL is shorthand for "default from a sequence":

-- Roughly equivalent:
CREATE TABLE t (id BIGSERIAL PRIMARY KEY);

-- =>
CREATE SEQUENCE t_id_seq;
CREATE TABLE t (id BIGINT PRIMARY KEY DEFAULT nextval('t_id_seq'));
ALTER SEQUENCE t_id_seq OWNED BY t.id;

IDENTITY Columns

Standard SQL way to define identity:

CREATE TABLE t (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT
);

-- GENERATED BY DEFAULT AS IDENTITY allows explicit overrides.

DEFAULT and Business Logic

Keep defaults simple. Complex logic belongs in app code or a BEFORE INSERT trigger.

Recap

DEFAULT fills the blanks; GENERATED computes from other columns.

NOW(), gen_random_uuid(), constant literals
Computed STORED columns are great for derived data
Identity columns are the modern PK pattern

Quick Check

Which clause makes a column auto-filled with the current time on INSERT if no value is supplied?

Frequently asked questions

Is the “DEFAULT Values and Generated Columns” lesson free?

Yes — the full text of “DEFAULT Values and Generated Columns” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “DEFAULT Values and Generated Columns”?

Provide DEFAULTs (NOW(), uuid_generate_v4(), 'pending') and compute values on the fly with GENERATED ALWAYS AS columns. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “DEFAULT Values and Generated Columns” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy