DEFAULT Values

Set automatic values for columns when INSERT omits them.

Overview

A DEFAULT value is used automatically when an INSERT does not provide a value for that column. Defaults can be literal values (0, 'active', FALSE), functions (NOW(), gen_random_uuid()), or expressions. Defaults simplify INSERT statements and ensure sensible initial values without requiring the application to explicitly set them. Common defaults: created_at TIMESTAMPTZ DEFAULT NOW(), is_active BOOLEAN DEFAULT TRUE, status TEXT DEFAULT 'pending', version INTEGER DEFAULT 1. You can set or change a column's default at any time using ALTER TABLE.

Key Points

•DEFAULT val is used when INSERT omits the column
•Can be a literal, function call, or expression
•Common: DEFAULT NOW(), DEFAULT TRUE, DEFAULT 0
•DEFAULT NULL is the implicit default when no DEFAULT is specified
•Explicit NULL in INSERT overrides the DEFAULT
•Change default: ALTER TABLE t ALTER COLUMN col SET DEFAULT new_val

Examples

Columns with defaults

CREATE TABLE posts (
  id         BIGSERIAL    PRIMARY KEY,
  title      TEXT         NOT NULL,
  status     TEXT         NOT NULL DEFAULT 'draft',
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  view_count INTEGER      NOT NULL DEFAULT 0,
  published  BOOLEAN      NOT NULL DEFAULT FALSE
);

-- INSERT without specifying defaults
INSERT INTO posts (title) VALUES ('Hello World');
-- status='draft', created_at=now(), view_count=0, published=false

Overriding and changing defaults

-- Explicit NULL overrides DEFAULT
INSERT INTO posts (title, status) VALUES ('Draft Post', NULL);
-- status = NULL (even though DEFAULT is 'draft')

-- Change the default
ALTER TABLE posts ALTER COLUMN status SET DEFAULT 'published';

-- Remove a default
ALTER TABLE posts ALTER COLUMN view_count DROP DEFAULT;

DEFAULT Values

Overview

Key Points

Examples

Exercises

Knowledge Check

An INSERT explicitly provides NULL for a column that has DEFAULT 0. What value is stored?