Data types, tables and constraints

Choosing numeric, text and time types, uuid versus identity, domains, CHECK constraints, foreign keys and the deferrable options that matter.

Choosing types

create table invoice (
  id           bigint generated always as identity primary key,
  public_id    uuid not null default gen_random_uuid(),
  customer_id  bigint not null references customer(id) on delete restrict,
  number       text not null,
  total_cents  bigint not null check (total_cents >= 0),
  tax_rate     numeric(5,4) not null default 0,
  currency     char(3) not null default 'USD',
  status       text not null default 'draft'
                 check (status in ('draft','issued','paid','void')),
  issued_at    timestamptz,
  due_on       date,
  metadata     jsonb not null default '{}'::jsonb,
  created_at   timestamptz not null default now(),
  constraint invoice_number_unique unique (number)
);

create unique index invoice_public_id_key on invoice (public_id);
create index invoice_customer_idx on invoice (customer_id, issued_at desc);

Constraints that do real work

-- a domain carries a rule everywhere it is used
create domain email_address as text
  check (value ~ '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$');

alter table customer add column contact email_address not null;

-- a partial unique index: only one active subscription per account
create unique index one_active_sub_per_account
  on subscription (account_id)
  where ended_at is null;

-- exclude overlapping ranges
create extension if not exists btree_gist;
alter table booking add constraint no_overlap
  exclude using gist (room_id with =, during with &&);

-- deferrable foreign key for a circular load
alter table employee
  add constraint employee_manager_fk foreign key (manager_id)
  references employee(id) deferrable initially deferred;

-- validation without locking writes for long
alter table invoice add constraint invoice_total_check check (total_cents >= 0) not valid;
alter table invoice validate constraint invoice_total_check;

• A partial unique index is the standard way to enforce a conditional rule, such as one active record per parent.
• An exclusion constraint enforces overlap rules that a unique index cannot express - booking systems and pay periods are the usual cases.
• not valid then validate constraint takes only a brief lock and scans existing rows separately.
• A foreign key with on delete cascade is convenient and dangerous: it deletes rows without your application seeing the deletion, including in an audit trail.

Changing a table safely

-- add a column with a default: fast since PostgreSQL 11 (no table rewrite)
alter table invoice add column notes text;

-- add a NOT NULL column in three safe steps
alter table invoice add column channel text;
update invoice set channel = 'web' where channel is null;   -- batch this in production
alter table invoice alter column channel set not null;
alter table invoice alter column channel set default 'web';

-- add a constraint without a long exclusive lock
alter table invoice add constraint channel_check
  check (channel in ('web','mobile','import')) not valid;
alter table invoice validate constraint channel_check;

-- set a fill factor for update-heavy tables
alter table counter set (fillfactor = 80);

• Adding a column with a constant default is a metadata-only change in modern PostgreSQL; a volatile default such as now() still rewrites nothing but evaluates per row for existing rows.
• Changing a column type usually rewrites the table and takes an ACCESS EXCLUSIVE lock. Add a new column, backfill in batches, swap, then drop.
• Set a short lock_timeout before DDL so a migration waiting behind a long query fails instead of blocking every reader behind it.
• create index concurrently must not run inside a transaction block; most migration tools need an explicit opt-out for it.

FAQ

Related

psql and the basics of SQL Indexes: B-tree, GIN, GiST, partial and expression

Last refreshed 2026-09-18.