DEFAULT value

A DEFAULT value is a value a database column automatically takes on when an INSERT statement doesn't explicitly specify a value for it.

Overview

Declaring a DEFAULT for a column saves every INSERT statement from having to specify a value explicitly, and centralizes a column's fallback behavior in one place in the schema instead of scattering it across application code. Defaults are only applied when a value is omitted (or, in most databases, when DEFAULT is used explicitly in the VALUES list) — they don't override an explicitly provided value, including an explicit NULL.

Syntax

Copy code
CREATE TABLE events (
    id INTEGER,
    status VARCHAR DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT current_timestamp
);

INSERT INTO events (id) VALUES (1);
-- status becomes 'pending', created_at becomes the current time

Defaults with Expressions and Sequences

Defaults aren't limited to constants — they can be any expression, including a call to generate a surrogate key from a sequence:

Copy code
CREATE SEQUENCE event_seq START 1;

CREATE TABLE events (
    id INTEGER DEFAULT nextval('event_seq'),
    status VARCHAR DEFAULT 'pending'
);

DuckDB Support

DuckDB supports DEFAULT values with constant literals, expressions, and sequence calls via nextval(), matching the syntax above. This makes DEFAULT nextval('seq_name') DuckDB's standard pattern for auto-incrementing surrogate keys, since DuckDB doesn't have a dedicated SERIAL or AUTO_INCREMENT type.

Related terms

Sequence →

A sequence is a database object that generates a series of unique numeric values, most commonly used to auto-generate primary key IDs.

INSERT statement →

The INSERT statement is a fundamental SQL command used to add new rows of data into a table.

ALTER TABLE statement →

The ALTER TABLE statement allows you to modify the structure of an existing database table without having to recreate it from scratch.

Surrogate key →

A surrogate key is a system-generated identifier — typically a sequential integer or UUID — assigned to a row that has no business meaning of its own, used as the primary key in dimension and fact tables.

COALESCE →

COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns.

CREATE TABLE statement →

The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types,…

FAQS

No. A DEFAULT only applies when a column's value is omitted from the INSERT; an explicitly provided value, including an explicit NULL, is used instead.

Create a SEQUENCE and set the column's DEFAULT to nextval('sequence_name'), since DuckDB doesn't have a SERIAL type.