DEFAULT Constraint in PostgreSQL

The PostgreSQL DEFAULT constraint lets you define a fallback value for a column that is supplied automatically when an INSERT statement does not provide an explicit value for that column. Defaults keep your schema self-documenting and move data-integrity logic down to the storage layer, where it is enforced regardless of which application, migration tool, or admin script is writing rows.

Syntax

Define a default when creating a table:

CREATE TABLE table_name (
  column_name data_type DEFAULT default_value
);

Add or change a default on an existing column:

ALTER TABLE table_name
  ALTER COLUMN column_name SET DEFAULT default_value;

Remove a default entirely:

ALTER TABLE table_name
  ALTER COLUMN column_name DROP DEFAULT;

Practical Example

Create an orders table where status defaults to 'pending' and timestamps are set automatically:

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  status      VARCHAR(30)  NOT NULL DEFAULT 'pending',
  total_cents INT          NOT NULL DEFAULT 0,
  notes       TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Insert a row specifying only the required field:

INSERT INTO orders DEFAULT VALUES RETURNING *;

 order_id | status  | total_cents | notes |          created_at           |          updated_at
----------+---------+-------------+-------+-------------------------------+-------------------------------
        1 | pending |           0 |       | 2026-04-07 14:05:32.410112+00 | 2026-04-07 14:05:32.410112+00

Insert a row and override only the columns you need:

INSERT INTO orders (status, total_cents)
VALUES ('confirmed', 4999)
RETURNING *;

You can also pass DEFAULT as an explicit keyword in a VALUES list to use the configured default while specifying other columns:

INSERT INTO orders (status, total_cents, notes)
VALUES ('pending', DEFAULT, 'Rush order')
RETURNING *;

Add a default to an existing column:

ALTER TABLE orders
  ALTER COLUMN status SET DEFAULT 'draft';

Remove the default:

ALTER TABLE orders
  ALTER COLUMN status DROP DEFAULT;

Inspect current column defaults:

SELECT column_name, column_default, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Expressions, Functions, and Complex Defaults

Default values can be any valid PostgreSQL expression evaluated at insert time:

CREATE TABLE products (
  product_id   UUID        NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  sku          VARCHAR(30) NOT NULL DEFAULT CONCAT('SKU-', TO_CHAR(NOW(), 'YYYYMMDD'), '-', nextval('sku_seq')),
  product_name VARCHAR(200) NOT NULL,
  is_active    BOOLEAN      NOT NULL DEFAULT TRUE,
  metadata     JSONB               DEFAULT '{}'
);

A JSONB column defaulting to an empty object is useful for optional configuration that may not be populated on every row:

CREATE TABLE event_settings (
  event_id     INT  PRIMARY KEY REFERENCES events (event_id),
  display_opts JSONB NOT NULL DEFAULT '{"theme": "light", "public": true}'
);

Using CURRENT_TIMESTAMP vs NOW() vs a static value — all three differ slightly:

Testing with Vela

Changing defaults on production tables — especially removing a default from a NOT NULL column — can break application code that relies on the implicit fallback. Vela’s database branching lets you test the default change on a copy of production data and run your full integration test suite against the modified schema before applying it to the live database. This is particularly valuable when adding a NOT NULL column with a DEFAULT to a large table, where PostgreSQL 11+ stores the default in the catalog (no table rewrite) but older migrations may not be aware of this optimization.

Production Tips

• Prefer server-side defaults over application-side defaults. The database enforces them even when data is written through migrations, admin scripts, or third-party tools that bypass your application layer.
• Use DEFAULT CURRENT_TIMESTAMP for created_at columns. For updated_at, use a trigger or handle it in the application — PostgreSQL has no built-in auto-update mechanism for DEFAULT.
• In PostgreSQL 11+, adding a NOT NULL column with a DEFAULT does not rewrite the table. The default is stored in the system catalog and applied on read until a row is physically updated. On PostgreSQL 10 and earlier, the same operation rewrites every row.
• Changing or removing a default is a metadata-only operation — it does not affect existing rows, only future inserts.
• When you add a column with DEFAULT NULL (or omit DEFAULT entirely) to a NOT NULL column, you must also provide a non-null default to avoid breaking existing INSERT statements that omit the column.
• Use \d table_name or query information_schema.columns.column_default to audit which columns have defaults and confirm the correct default expression after a migration.