skip to content

In PostgreSQL, what does a domain with a CHECK constraint give you over repeating the CHECK on every column, and what goes wrong when you tighten it on a large schema?

level: seniorimportance: should knowfreq 32%

answer

  1. one named rule, many columns
  2. checked on conversion only
  3. NOT NULL belongs on the column
  4. NOT VALID, then VALIDATE
  5. containers block the check

basics

~20 s

A domain is a named type whose CHECK runs wherever a value is converted to it, so one rule serves every column and function. Tightening it scans every column of that domain under SHARE locks that block writes.

solid answer

~50 s

A domain such as `recipe_slug AS text CHECK (VALUE ~ '...')` defines the rule once; every column, function argument or cast that uses the type gets it, and a violation raises `23514` with `value for domain ... violates check constraint`. Domains also work over arrays and composites, which is how a composite value gets constraints its table row type lacks. Tightening is where it bites. `ALTER DOMAIN ADD CONSTRAINT` checks every column of the domain in every table, and the scan holds a `SHARE` lock that blocks writes. `NOT VALID` skips existing rows while new values are checked at once; after older transactions finish, `VALIDATE CONSTRAINT` scans, still under `SHARE`. Both fail outright if the domain sits inside an array, composite or range column. Keep `NOT NULL` on columns, because domain checks run only on conversion and outer joins yield unchecked NULLs.

code

sql · 17 lines
sql
CREATE DOMAIN recipe_slug AS text
  CHECK (VALUE ~ '^[a-z0-9]+(-[a-z0-9]+)*$');
CREATE DOMAIN tag_list AS text[]
  CHECK (cardinality(VALUE) <= 20);

CREATE TABLE recipe (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug recipe_slug NOT NULL UNIQUE,
  tags tag_list NOT NULL DEFAULT '{}'
);

-- new and updated slugs are checked now; existing rows are not
ALTER DOMAIN recipe_slug
  ADD CONSTRAINT slug_len CHECK (char_length(VALUE) <= 80) NOT VALID;

-- later: scans every recipe_slug column under SHARE locks
ALTER DOMAIN recipe_slug VALIDATE CONSTRAINT slug_len;

go deeper

for a junior

Know that CREATE DOMAIN names a base type plus a CHECK using VALUE, and that a violation raises a check_violation error.

for a middle

Explain that checks run on conversion only, why NOT NULL belongs on columns, and how a domain over a composite adds constraints the row type lacks.

for a senior

Plan tightening on a big schema: NOT VALID, wait for older transactions, VALIDATE under SHARE locks, and the container-column restriction.

for a principal

Decide which business rules become shared domains and which stay per table, weighing reuse against the lock cost of every future change.

## What a domain is A **domain** is a user-defined type based on an **underlying type**, optionally with constraints that restrict its values. Otherwise it behaves like the underlying type: operators and functions of `text` work on a domain over `text`, with the value cast down automatically. The underlying type can be a base type, an enum, an array, a composite, a range or another domain. On a recipe site, rules such as "a slug is lowercase words joined by hyphens" or "a tag list holds at most 20 tags" apply to many columns: recipe slugs, collection slugs, URL redirects, function parameters. A domain states each rule once, so a rule change is one `ALTER DOMAIN` instead of a hunt through every table's `CHECK`. A violation raises SQLSTATE `23514` (`check_violation`) with the message `value for domain recipe_slug violates check constraint ...`. ## Where the CHECK runs, and where it does not PostgreSQL checks domain constraints **when a value is converted to the domain type**, and the manual spells out two consequences: - **NULL leaks past a domain NOT NULL.** A column of a NOT NULL domain can still read as NULL on the nullable side of an outer join, and `INSERT INTO recipe (slug) VALUES ((SELECT slug FROM recipe WHERE false))` succeeds, because the empty sub-select yields a NULL already typed as the domain. Best practice is to let the domain accept NULL and put `NOT NULL` on the columns. - **CHECK expressions are assumed immutable.** If the CHECK calls your own function and you change what it accepts, PostgreSQL does not recheck stored values; the inconsistency surfaces later, when a dump fails to restore. Drop the constraint, change the function, and add the constraint again so stored data is rechecked. The manual also advises writing domain CHECK expressions that cannot throw errors. ## Domains over arrays and composites Every table defines a **composite type** with the table's name, and you can create standalone ones with `CREATE TYPE nutrition AS (kcal integer, protein_g numeric)`. A composite value carries **no constraints**: a table's CHECKs do not apply to its row type when it is used elsewhere. A domain over the composite is the documented workaround. Reading a field of a composite column needs parentheses, `(nutrition).kcal`, because `nutrition.kcal` is parsed as table `nutrition`, column `kcal`. A domain over an array, such as `tag_list AS text[] CHECK (cardinality(VALUE) <= 20)`, gives array columns the size limit their declared `text[20]` never enforced. ## Tightening a domain on a live schema `ALTER DOMAIN ... ADD CONSTRAINT` without `NOT VALID` verifies every column that uses the domain, in **every table**. The check takes a `SHARE` lock on each of those tables for the scan, which blocks `INSERT`, `UPDATE` and `DELETE` there. The documented safe sequence is: 1. `ALTER DOMAIN recipe_slug ADD CONSTRAINT slug_len CHECK (char_length(VALUE) <= 80) NOT VALID;` New and updated values are checked immediately; existing rows are not. 2. Commit, and wait until every transaction that started before that commit has finished, because the check cannot see rows written by uncommitted transactions. 3. Fix the violating rows the scan would find. 4. `ALTER DOMAIN recipe_slug VALIDATE CONSTRAINT slug_len;` This scans every column of the domain, again under `SHARE` locks. `NOT VALID` makes the check correct under concurrency, but it does not make validation write-friendly the way `ALTER TABLE ... VALIDATE CONSTRAINT` is. On a domain used by a few huge tables, schedule step 4. ## The container-type restriction `ALTER DOMAIN ADD CONSTRAINT`, `VALIDATE CONSTRAINT` and `SET NOT NULL` **fail outright** if the domain, or a domain derived from it, is used inside a container column anywhere in the database: an array of the domain, a composite with a field of it, or a range over it. PostgreSQL cannot yet verify nested values, so a `recipe_slug[]` column blocks every future tightening of `recipe_slug`. ## Domain, per-column CHECK or enum | Need | Better tool | |---|---| | One format rule shared by many columns and functions | domain with CHECK | | A rule specific to one table, possibly across columns | table `CHECK` | | A small, ordered, stable set of labels | enum | | A rule that must be tightened often on huge tables | weigh the scan locks first |

  • Why is NOT NULL on the domain itself discouraged in PostgreSQL?
    Domain constraints run only when a value is converted to the domain. A NULL produced on the nullable side of an outer join, or by an empty scalar sub-select in an `INSERT`, is already typed as the domain and is never checked, so `INSERT ... VALUES ((SELECT slug FROM recipe WHERE false))` succeeds. The manual recommends nullable domains with `NOT NULL` on the columns.
  • The recipe_slug CHECK calls your own function, and you change the function to accept fewer slugs. What happens to stored slugs?
    Nothing rechecks them: PostgreSQL treats CHECK conditions as immutable and evaluates them only when a value is converted to the domain. Stored slugs that now violate the rule stay put until a dump and restore fails on them. Drop the constraint, change the function, and re-add it so stored data is checked.
  • How do composite types interact with constraints?
    Every table defines a composite row type, but the table's constraints do not apply to values of that type used elsewhere, such as a composite column in another table. To constrain a composite value, create a domain over the composite with CHECKs on its fields. Field access then needs parentheses: `(nutrition).kcal`.

  • Tightening a domain's CHECK only affects rows written afterwards; existing rows are never checked.
  • ALTER DOMAIN ... VALIDATE CONSTRAINT lets writes continue, like ALTER TABLE's VALIDATE.
  • A NOT NULL domain guarantees a column of that type never reads as NULL.
  • A table's CHECK constraints also apply to its row type used as a composite column elsewhere.
  • Domain constraints are rechecked automatically when a function they call changes.