Custom types and domains — what do they give you? — Cracked Java
// PostgreSQL · PostgreSQL Data Types
MidTheory

Custom types and domains — what do they give you?

Domains and custom types let you push rules and structure into the type system, so a constraint lives in one place instead of being copy-pasted into every table and every CHECK. They're underused, and knowing them signals you think about schemas as a system, not a pile of columns.

DOMAIN — a base type plus a reusable constraint

A DOMAIN is an existing type with optional NOT NULL, a DEFAULT, and CHECK constraints attached, given a name you reuse everywhere:

CREATE DOMAIN email AS text
    CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+
#x27;);

CREATE DOMAIN positive_int AS integer
    CHECK (VALUE > 0);

CREATE TABLE users (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    contact_email email NOT NULL,        -- format enforced automatically
    login_count   positive_int NOT NULL DEFAULT 1
);

Now every email column shares one validation rule. Change the regex once with ALTER DOMAIN ... ADD/DROP CONSTRAINT and it applies everywhere — and Postgres re-validates existing data when you add a constraint. This is the main payoff: DRY, centrally-managed invariants instead of duplicated CHECK clauses.

One sharp edge: a domain CHECK and NULL. NOT NULL on a domain helps, but a domain constraint can still be bypassed by a NULL flowing through certain expressions/casts — keep NOT NULL on the column too when nullability matters.

Composite (row) types

CREATE TYPE builds a structured value with named fields — useful as a function return type or a single structured column:

CREATE TYPE address AS (
    street text,
    city   text,
    zip    text
);

CREATE TABLE customer (shipping address);
SELECT (shipping).city FROM customer;   -- access a field

Every table also implicitly defines a composite type of its row shape. Composite columns are occasionally handy but often better modeled as plain columns (you can't easily index or constrain individual subfields). Their best use is function return values and RETURNS TABLE.

Other custom types

  • Enumerated types (CREATE TYPE ... AS ENUM) — covered separately.
  • Range types (int4range, tsrange, daterange) — built-in, and you can define your own; great for "valid between two timestamps" with exclusion constraints.
  • Base types via C extensions — rare in application work.

Mark your status