Topic 25 of 52
DEFAULT
Overview
DEFAULT values automatically populate column values when no value is provided during INSERT. They reduce boilerplate in INSERT statements and ensure columns always have valid initial values.
Syntax
sql
-- Static defaults
status VARCHAR(20) DEFAULT 'pending'
is_active BOOLEAN DEFAULT TRUE
quantity INT DEFAULT 1
discount DECIMAL(5,2) DEFAULT 0.00
-- Dynamic defaults (evaluated at insert time)
created_at TIMESTAMPTZ DEFAULT NOW() -- current timestamp
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
id UUID DEFAULT gen_random_uuid()
-- Sequence-based default
id SERIAL PRIMARY KEY -- shorthand for SEQUENCE default
-- Setting default on existing column
ALTER TABLE products ALTER COLUMN in_stock SET DEFAULT TRUE;
ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 'normal';
-- Removing default
ALTER TABLE products ALTER COLUMN discount DROP DEFAULT;
-- Using DEFAULT in INSERT
INSERT INTO products (name, price) VALUES ('Widget', 99.99);
-- status, created_at, etc. get their defaults automatically
INSERT INTO products (name, price, status) VALUES ('Widget', 99.99, DEFAULT);
-- Explicitly trigger default valueCommon Pitfalls
- DEFAULT NOW() is evaluated at row insert time — use it for created_at. For updated_at, you need a trigger to update it on every UPDATE.
- DEFAULT only applies on INSERT — it does NOT update existing rows when you add/change a default on an existing column.
- Interview tip: UUID DEFAULT gen_random_uuid() generates unique IDs automatically — much better than generating UUIDs in application code.
Real-World Example
A subscription table with sensible defaults:
example
sql
CREATE TABLE subscriptions (
id BIGSERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(id),
plan VARCHAR(20) NOT NULL DEFAULT 'free'
CHECK (plan IN ('free','basic','pro','enterprise')),
billing_cycle VARCHAR(10) NOT NULL DEFAULT 'monthly'
CHECK (billing_cycle IN ('monthly','annual')),
monthly_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
trial_ends_at TIMESTAMPTZ DEFAULT NOW() + INTERVAL '14 days', -- 14-day trial
is_active BOOLEAN NOT NULL DEFAULT TRUE,
auto_renew BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Insert without specifying defaults
INSERT INTO subscriptions (user_id, plan)
VALUES (42, 'pro');
-- Gets: billing_cycle='monthly', is_active=TRUE, auto_renew=TRUE,
-- trial_ends_at=NOW()+14days, created_at=NOW()