DEFAULT Constraint

The PostgreSQL DEFAULT constraint lets you define a fallback value for a column that is used automatically when an INSERT statement does not supply an explicit value. This keeps your schema self-documenting and reduces application-side boilerplate.

Defining a DEFAULT value when creating a table

Add the DEFAULT clause directly to the column definition:

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,
  price DECIMAL(19,2) NOT NULL DEFAULT 0
);

Inserting a row without specifying price automatically stores 0.00:

INSERT INTO products (name) VALUES ('Laptop') RETURNING *;

id |  name  | price
----+--------+-------
 1 | Laptop |  0.00

You can also pass the DEFAULT keyword explicitly in the VALUES list:

INSERT INTO products (name, price) VALUES ('Smartphone', DEFAULT) RETURNING *;

Using expressions and functions as defaults

Default values can be any valid PostgreSQL expression evaluated at insert time. A common pattern is recording the creation timestamp automatically:

CREATE TABLE logs (
  id         SERIAL PRIMARY KEY,
  message    TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO logs (message) VALUES ('Started the server') RETURNING *;

id |      message       |         created_at
----+--------------------+----------------------------
 1 | Started the server | 2024-03-15 10:22:48.680802

JSONB columns can default to an empty object, useful for optional configuration:

CREATE TABLE settings (
  id            SERIAL PRIMARY KEY,
  name          VARCHAR(50) NOT NULL,
  configuration JSONB DEFAULT '{}'
);

Modifying defaults on existing tables

Use ALTER TABLE ... ALTER COLUMN ... SET DEFAULT to add or change a default on an existing column:

ALTER TABLE settings
ALTER COLUMN configuration SET DEFAULT '{}';

To remove the default entirely:

ALTER TABLE settings
ALTER COLUMN configuration DROP DEFAULT;

Production tips

• Prefer server-side defaults over application-side defaults — the database enforces them even when data is inserted through migrations, scripts, or other clients.
• Use DEFAULT CURRENT_TIMESTAMP for created_at columns; pair it with a trigger or application logic for updated_at since PostgreSQL does not have a built-in auto-update mechanism.
• Adding a DEFAULT to an existing NOT NULL column avoids a table rewrite in PostgreSQL 11+ — the default is stored in the catalog and applied on read until the column is updated.
• Test default behavior in an isolated branch database before applying schema changes to production.

Reference: PostgreSQL documentation — Default Values.