SQL / DEFINING TABLES AND CONSTRAINTS
DEFAULT values and what fills the gap
Declare column defaults, predict when the database fills them in, and explain why an explicit NULL bypasses them while ALTER TABLE leaves old rows alone.
What you will learn
- Omit a column from the INSERT column list so its DEFAULT expression is evaluated
- Explain why INSERT ... VALUES (NULL) stores NULL and never reaches the default
- Use the DEFAULT keyword in VALUES and UPDATE ... SET col = DEFAULT deliberately
- Backfill existing rows yourself after ALTER COLUMN ... SET DEFAULT
Understanding DEFAULT values and what fills the gap
A DEFAULT clause attaches an expression to a column, and the engine evaluates that expression whenever an INSERT does not mention the column. Columns declared without a DEFAULT still have one: the implicit default is NULL, which is why omitting a nullable column is legal while omitting a NOT NULL column with no default is an error. The clause lives in the catalog beside the column definition, so it is part of the table's shape rather than part of any stored row.
The trigger is absence, not emptiness. If the column appears in the INSERT column list, whatever you supply is what gets stored, and NULL is something you supplied; no engine has a rule that swaps a NULL for the declared default. That one fact explains most complaints that a default is not working: a driver or CSV loader that sends every column, with an empty field for whatever the user left blank, turns each default into a NULL, or into a not-null violation when the column also carries NOT NULL. If you want to keep the column in the list and still get its default, put the word DEFAULT in the value position.
Because a default is an expression re-evaluated per row at insert time, DEFAULT CURRENT_TIMESTAMP stamps each insert instead of freezing the moment the table was created, and a default of nextval or gen_random_uuid gives every row a distinct value. The same reasoning explains two limits: UPDATE never consults a default, since an UPDATE always supplies a value, and changing a default only changes what future inserts do, because existing rows already hold values. Most engines also reject defaults that read other columns or run subqueries, since the expression is evaluated in isolation before the row exists as a whole, so row-dependent values belong in a generated column or a trigger.
The trigger is absence, not emptiness.
CREATE TABLE support_ticket (
id integer PRIMARY KEY,
subject text NOT NULL,
priority integer DEFAULT 3,
status text DEFAULT 'open',
assignee text
);
-- priority and status are absent, so both defaults are evaluated
INSERT INTO support_ticket (id, subject) VALUES (1, 'Login loops');
-- status is present and holds NULL, so its default is never consulted
INSERT INTO support_ticket (id, subject, status) VALUES (2, 'Slow export', NULL);
-- the DEFAULT keyword keeps the column listed and still asks for its default
INSERT INTO support_ticket (id, subject, priority, status)
VALUES (3, 'Typo on invoice', 5, DEFAULT);
SELECT id,
priority,
COALESCE(status, 'NULL') AS status,
COALESCE(assignee, 'NULL') AS assignee
FROM support_ticket
ORDER BY id;A DEFAULT is consulted only when a column is absent from the INSERT, so any value you supply, including NULL, wins over it.
Worked examples
Adding a default later
Shows that setting a default on an existing column changes future inserts only.
CREATE TABLE device (
id integer PRIMARY KEY,
firmware text
);
INSERT INTO device (id) VALUES (1);
ALTER TABLE device ALTER COLUMN firmware SET DEFAULT '1.0.0';
INSERT INTO device (id) VALUES (2);
SELECT id, COALESCE(firmware, 'NULL') AS firmware
FROM device
ORDER BY id;Example explained
Line 1Row 1 is inserted while firmware has no declared default, so it stores the implicit default NULL.
Line 2ALTER COLUMN ... SET DEFAULT edits the column definition in the catalog, not the rows already stored.
Line 3Row 2 omits firmware after the change, so the new default expression runs and 1.0.0 is written.
Line 4Making row 1 agree requires an explicit UPDATE device SET firmware = '1.0.0' WHERE firmware IS NULL.
Defaults are evaluated per row
Shows that a default expression runs at each insert rather than once at table creation.
CREATE TABLE audit_entry (
id integer PRIMARY KEY,
action text NOT NULL,
attempt integer DEFAULT 1,
logged_at timestamp DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO audit_entry (id, action) VALUES (1, 'login');
INSERT INTO audit_entry (id, action, attempt) VALUES (2, 'login', 2);
SELECT id,
attempt,
CASE WHEN logged_at IS NULL THEN 'empty' ELSE 'stamped' END AS logged_at
FROM audit_entry
ORDER BY id;Example explained
Line 1CURRENT_TIMESTAMP is stored as an expression, so it is not frozen at the moment CREATE TABLE ran.
Line 2Each INSERT re-evaluates it, which is how both rows hold a timestamp nobody typed.
Line 3Row 2 supplies attempt, so the literal default 1 is skipped for that row alone: defaults are decided per row and per column.
Line 4The timestamp would print differently on every run, so the query reports whether it was filled instead of showing it.
UPDATE and the DEFAULT keyword
Shows that an UPDATE never falls back to a default unless you name it.
CREATE TABLE setting (
name text PRIMARY KEY,
scope text DEFAULT 'global'
);
INSERT INTO setting (name) VALUES ('retries');
UPDATE setting SET scope = NULL WHERE name = 'retries';
SELECT name, COALESCE(scope, 'NULL') AS scope FROM setting;
UPDATE setting SET scope = DEFAULT WHERE name = 'retries';
SELECT name, COALESCE(scope, 'NULL') AS scope FROM setting;Example explained
Line 1The INSERT names only the name column, so scope is filled from its default and starts as global.
Line 2An UPDATE always supplies a value, so assigning NULL stores NULL with no fallback to the default.
Line 3SET scope = DEFAULT is the one way an UPDATE asks the catalog for the declared default value.
Line 4Retyping the literal 'global' would work today but drift the moment someone changes the column default.
Important notes
ADD COLUMN and SET DEFAULT differ: adding a new column with a default materialises that value for existing rows, while setting a default on a column that already exists leaves them untouched.
CURRENT_TIMESTAMP as a default is the transaction start time in PostgreSQL, so rows inserted in one transaction share it; SQLite accepts neither the bare DEFAULT keyword in VALUES nor SET col = DEFAULT.
Common mistakes
Letting the application send NULL for fields the user left blank: the default is bypassed, so you store NULL where you expected 'open', or get a not-null violation that looks like the default failing.
Assuming ALTER COLUMN ... SET DEFAULT repairs history: old rows keep their NULLs, so reports and code that trust the column break on legacy data only.
Writing a default that reads the row, such as DEFAULT (unit_price * quantity) or DEFAULT (SELECT max(id) FROM ...): the DDL is rejected because the expression is evaluated with no view of the row, and the fix is a generated column or a trigger.
Try it yourself
Change, predict, then run
Create invitation (id integer PRIMARY KEY, email text NOT NULL, role text DEFAULT 'viewer', invited_by text DEFAULT 'system') and insert three rows: one omitting both defaulted columns, one passing NULL for role, and one passing the keyword DEFAULT for invited_by. Select all rows and state which cell holds NULL and why the default did not fill it.
Open the SQL workspaceCheck your understanding
A column is declared status text DEFAULT 'open' with no NOT NULL. An application inserts a row and explicitly sends NULL for status. What ends up stored?
- 'open', because a NULL means the column has no value and the default takes over
- The insert is rejected, since a column with a DEFAULT cannot be given NULL
- NULL, because the column was supplied a value and the default applies only when the column is absent
- 'open', but only once the next UPDATE touches the row
Show answer
The default is consulted only when the column is missing from the INSERT; NULL is a value you supplied, so it is stored as it stands. The first option describes what people expect, and it matches what an omitted column looks like, but no engine substitutes a default for a NULL that arrived in the statement: the only ways to get 'open' are to leave status out of the column list or to write DEFAULT in its value position.