The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Create a constrained domainCREATE DOMAIN app.email_address AS text CONSTRAINT email_nonempty CHECK (btrim(VALUE) <> '');View examples
Use a domain in a tableemail app.email_address NOT NULLView examples
Stage a domain checkALTER DOMAIN app.email_address ADD CONSTRAINT email_length CHECK (octet_length(VALUE) <= 320) NOT VALID;View examples
Validate a domain checkALTER DOMAIN app.email_address VALIDATE CONSTRAINT email_length;View examples
Create an enum typeCREATE TYPE app.order_state AS ENUM ('pending', 'paid', 'shipped', 'cancelled');View examples
Append an enum labelALTER TYPE app.order_state ADD VALUE IF NOT EXISTS 'returned';View examples
Position an enum labelALTER TYPE app.order_state ADD VALUE 'processing' AFTER 'pending';View examples
Rename an enum labelALTER TYPE app.order_state RENAME VALUE 'shipped' TO 'dispatched';View examples
Order by enum semanticsSELECT id, state FROM app.orders ORDER BY state, id;View examples
Create a composite typeCREATE TYPE app.money_amount AS (amount numeric(18,2), currency_code text);View examples
Construct a typed compositeROW(49.90, 'USD')::app.money_amountView examples
Read a composite fieldSELECT (price).amount, (price).currency_code FROM app.products;View examples
Add a composite attributeALTER TYPE app.money_amount ADD ATTRIBUTE tax_included boolean CASCADE;View examples
Grant type usageGRANT USAGE ON TYPE app.order_state, app.email_address, app.money_amount TO app_writer;View examples
Cast text explicitlySELECT $1::text::app.order_state;View examples
Inventory custom typesSELECT n.nspname, t.typname, t.typtype FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace WHERE n.nspname = 'app';View examples
Inspect enum labelsSELECT enumlabel, enumsortorder FROM pg_enum WHERE enumtypid = 'app.order_state'::regtype ORDER BY enumsortorder;View examples

PostgreSQL domains, enums, and composite types encode different contracts in the database. Domains reuse scalar validation, enums model small and deliberately stable ordered sets, and composites package named fields for rows and function interfaces. Their definitions become dependencies shared by tables, functions, views, clients, dumps, and replicas, so choose the least rigid abstraction that preserves the invariant and evolve it through reviewed migrations.

Step by step

Detailed examples

01

Use domains for reusable scalar invariants

A domain wraps one underlying type with defaults, collation, and CHECK constraints. Keep domain checks deterministic and limited to the value represented by VALUE; PostgreSQL assumes a CHECK condition remains stable and does not continuously recheck stored rows if a referenced function later changes. Prefer column-level NOT NULL because SQL null propagation and outer joins can still produce a null value that appears to have a not-null domain type. A column default overrides a domain default, so avoid hidden defaults in broadly reused domains.

Centralize a modest email storage contract
CREATE DOMAIN app.email_address AS text
  CONSTRAINT email_trimmed CHECK (VALUE = btrim(VALUE))
  CONSTRAINT email_nonempty CHECK (VALUE <> '')
  CONSTRAINT email_length CHECK (octet_length(VALUE) <= 320);

CREATE TABLE app.contacts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email app.email_address NOT NULL
);
Back to quick reference ↑
02

Stage domain constraints before validating existing data

Adding a domain CHECK normally scans every column using the domain. NOT VALID makes the new rule apply to subsequent conversions and writes without first certifying old rows; commit it, wait for transactions that began before that commit to finish, remediate existing violations, then validate. Validation scans stored values and can consume substantial I/O. PostgreSQL cannot currently add or validate these constraints when the domain is nested inside table columns through arrays, composites, ranges, or derived domains. If a constraint function's behavior changes, drop and re-add the constraint so stored values are checked under the new behavior.

Roll out a stricter check in two phases
ALTER DOMAIN app.email_address
  ADD CONSTRAINT email_has_at
  CHECK (position('@' IN VALUE) > 1) NOT VALID;

-- Commit, let pre-existing transactions finish, and repair old rows.
ALTER DOMAIN app.email_address
  VALIDATE CONSTRAINT email_has_at;
Back to quick reference ↑
03

Reserve enums for small and stable ordered vocabularies

Every enum type is distinct, its labels are case-sensitive, and comparisons follow declaration order rather than lexical order. This gives compact storage and strong validation but couples application code and migrations to a database-level vocabulary. Use a reference table with keys, metadata, foreign keys, and ordinary DML when values are tenant-defined, frequently removed, richly described, or independently deployable. Indexes can use enum ordering, so changing business meaning without changing labels can silently alter query semantics.

Declare and query an ordered workflow state
CREATE TYPE app.order_state AS ENUM
  ('pending', 'paid', 'shipped', 'cancelled');

CREATE TABLE app.orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  state app.order_state NOT NULL DEFAULT 'pending'
);

SELECT id, state
FROM app.orders
ORDER BY state, id;
Back to quick reference ↑
04

Treat enum evolution as a coordinated release

ADD VALUE can run inside a transaction, but the new label cannot be used until that transaction commits. Existing labels cannot be removed and their order cannot be rearranged without replacing the type and converting dependent columns. A value inserted BEFORE or AFTER can make comparisons involving the new label marginally slower; append when ordering permits. Coordinate prepared statements, connection pools, drivers that cache type metadata, and every writer before emitting a new label.

Commit the type change before writing the new value
BEGIN;
ALTER TYPE app.order_state
  ADD VALUE IF NOT EXISTS 'returned' AFTER 'shipped';
COMMIT;

-- Run only after the preceding transaction commits.
UPDATE app.orders
SET state = 'returned'
WHERE id = 4201;
Back to quick reference ↑
05

Use composites for structure, not standalone integrity

A composite type supplies field names and types but cannot declare NOT NULL, CHECK, UNIQUE, or foreign-key constraints. Table constraints apply to stored table rows, not to the same table's automatically created row type when it is used elsewhere. Construct values with ROW and an explicit cast, and parenthesize a composite expression before selecting a field. For public function APIs, remember that adding or changing an attribute changes the contract seen by callers and client decoders.

Package an amount and currency for a function boundary
CREATE TYPE app.money_amount AS (
  amount numeric(18,2),
  currency_code text
);

CREATE FUNCTION app.gross_price(app.money_amount, numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
RETURN ($1).amount * (1 + $2);

SELECT app.gross_price(
  ROW(49.90, 'USD')::app.money_amount,
  0.20
);
Back to quick reference ↑
06

Map composite changes across every dependency

ALTER TYPE can add, drop, rename, or retype composite attributes, but dependent functions, views, typed tables, generated columns, and client row decoders may still require coordinated changes. RESTRICT is the safer default; CASCADE can propagate an attribute change to typed tables and descendants, not automatically repair application assumptions. DDL takes locks on affected objects, and changing table column types can require ACCESS EXCLUSIVE locking, a rewrite, and a subsequent ANALYZE, so rehearse against production-sized data.

Discover dependencies before changing a composite
SELECT pg_catalog.pg_describe_object(
  d.classid, d.objid, d.objsubid
) AS dependent_object,
       d.deptype
FROM pg_catalog.pg_depend AS d
WHERE d.refclassid = 'pg_type'::regclass
  AND d.refobjid = 'app.money_amount'::regtype
ORDER BY dependent_object;

-- Apply only after reviewing the dependency inventory.
ALTER TYPE app.money_amount
  ADD ATTRIBUTE tax_included boolean RESTRICT;
Back to quick reference ↑
07

Control type use and avoid surprising implicit casts

Creating a type requires CREATE on its schema and USAGE on referenced attribute or underlying types; altering it requires ownership. For security, revoke schema CREATE from untrusted roles and grant USAGE ON TYPE only to roles that need the contract. Prefer explicit casts at text and JSON boundaries. Broad implicit casts can change overload resolution, hide invalid inputs, and make plans or behavior change when functions and extensions are added, so custom cast definitions deserve the same review as executable code.

Separate ownership from application use
REVOKE CREATE ON SCHEMA app FROM PUBLIC;
REVOKE ALL ON TYPE app.order_state FROM PUBLIC;
GRANT USAGE ON TYPE app.order_state TO app_writer;

PREPARE set_order_state(bigint, text) AS
UPDATE app.orders
SET state = $2::app.order_state
WHERE id = $1;
Back to quick reference ↑
08

Inventory catalogs and coordinate replicas before rollout

Use pg_type, pg_enum, pg_constraint, and pg_depend to audit definitions and consumers before migration. Performance can change when casts, comparison operators, enum ordering, or composite layouts alter planner choices, so benchmark representative queries. Physical streaming replication replays catalog changes, but compatible applications and any external libraries used by type functions must still exist on failover hosts. Logical replication does not replicate schema DDL, so create compatible types and enum labels on subscribers before publishers send rows that require them. Test pg_dump and restore ordering, then deploy additive type changes before application writes and destructive replacements only behind a controlled write boundary.

Audit types and enum order in an application schema
SELECT t.oid::regtype AS type_name, t.typtype,
       bt.oid::regtype AS base_type
FROM pg_catalog.pg_type AS t
JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace
LEFT JOIN pg_catalog.pg_type AS bt ON bt.oid = t.typbasetype
WHERE n.nspname = 'app'
ORDER BY t.oid::regtype::text;

SELECT enumlabel, enumsortorder
FROM pg_catalog.pg_enum
WHERE enumtypid = 'app.order_state'::regtype
ORDER BY enumsortorder;
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: CREATE TYPEpostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL: Enumerated Typespostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: CREATE DOMAINpostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: ALTER DOMAINpostgresql.org
  5. PostgreSQL Global Development GroupPostgreSQL: Composite Typespostgresql.org
  6. PostgreSQL Global Development GroupPostgreSQL: ALTER TYPEpostgresql.org
  7. PostgreSQL Global Development GroupPostgreSQL: Logical Replication Restrictionspostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback