Enums, domains and composite types

conceptThe concept

Enum: CREATE TYPE order_state AS ENUM ('pending', 'paid', 'shipped'). Values are stored as 4-byte OIDs, sort in declaration order (not alphabetically) and reject anything not in the list. You can add values (ALTER TYPE ... ADD VALUE ... BEFORE/AFTER) and rename them, but you cannot remove or reorder values without recreating the type.

Domain: CREATE DOMAIN email AS text CHECK (...) is a base type plus NOT NULL, DEFAULT and CHECK constraints. Every column, function argument and cast that uses the domain gets the rule, and ALTER DOMAIN ... ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT changes it everywhere.

Composite type: CREATE TYPE address AS (street text, city text, postcode text). Every table already has one with the same name as the table. Use them for functions that return several columns and occasionally for grouped columns; access fields with (col).field.

whyWhy it matters

Types push rules into the database where no application path can skip them. A domain for currency amounts or email addresses means every new table gets the rule for free, and an enum makes invalid states impossible to store while using a quarter of the space of the text label.

Use caseReal-world scenario

An order system uses an enum for order_state because the lifecycle is ordered and rarely changes, and WHERE state >= 'shipped' reads naturally. Amounts use a money_amount domain (numeric(12,2), non-negative) shared by orders, refunds and invoices. Product categories, which marketing changes weekly, live in a lookup table instead.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE TYPE order_state AS ENUM ('pending', 'paid', 'shipped');
ALTER TYPE order_state ADD VALUE 'delivered' AFTER 'shipped';
ALTER TYPE order_state ADD VALUE 'on_hold' BEFORE 'paid';
SELECT enum_range(NULL::order_state);
SELECT 'paid'::order_state > 'on_hold'::order_state AS paid_sorts_after_on_hold;
SELECT enumlabel, enumsortorder FROM pg_enum
WHERE enumtypid = 'order_state'::regtype ORDER BY enumsortorder;
ROLLBACK;

BEGIN;
CREATE TYPE order_state AS ENUM ('pending', 'paid', 'shipped');
SELECT 'refunded'::order_state;
ROLLBACK;

BEGIN;
CREATE DOMAIN money_amount AS numeric(12,2) NOT NULL CHECK (VALUE >= 0);
CREATE TABLE refund (id int PRIMARY KEY, amount money_amount);
CREATE TABLE invoice (id int PRIMARY KEY, amount money_amount);
INSERT INTO refund VALUES (1, 19.99);
INSERT INTO invoice VALUES (1, 120);
SELECT table_name, column_name, domain_name
FROM information_schema.columns
WHERE domain_name = 'money_amount' ORDER BY table_name;
ROLLBACK;

BEGIN;
CREATE DOMAIN email AS text CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[a-z]{2,}$');
CREATE TABLE signup (id int PRIMARY KEY, contact email);
INSERT INTO signup VALUES (1, 'asha@example.com');
INSERT INTO signup VALUES (2, 'not-an-email');
ROLLBACK;

BEGIN;
CREATE TYPE address AS (street text, city text, postcode text);
CREATE TABLE depot (id int PRIMARY KEY, addr address);
INSERT INTO depot VALUES (1, ROW('1 Dock Rd', 'Pune', '411001'));
SELECT (addr).city, addr FROM depot;
UPDATE depot SET addr.postcode = '411002' WHERE id = 1;
SELECT (d.addr).* FROM depot d;
ROLLBACK;

SELECT c AS whole_row, (c).city
FROM customers c
ORDER BY id
LIMIT 2;

internalsUnder the hood

Enum labels live in pg_enum with a float enumsortorder, which is how ADD VALUE BEFORE inserts between two existing values without renumbering. A value added in a transaction cannot be used in that same transaction unless the type itself was created in it ("unsafe use of new value"). Domains are checked on input and on cast to the domain; a domain over a composite or an array works too. Composite values are stored inline in the row as a nested tuple with their own header.

Avoid / preferDo this, not that

CREATE TYPE category AS ENUM ('shoes', 'bags', 'hats');
-- marketing adds and retires categories every month

CREATE TABLE category (code text PRIMARY KEY, label text NOT NULL, active boolean NOT NULL DEFAULT true);
ALTER TABLE products ADD FOREIGN KEY (category) REFERENCES category (code);

CREATE TABLE a (email text CHECK (email ~ '@'));
CREATE TABLE b (email text CHECK (email LIKE '%@%.%'));

CREATE DOMAIN email AS text CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[a-z]{2,}$');
CREATE TABLE a (email email);
CREATE TABLE b (email email);

PitfallsCommon mistakes

• Expecting enum values to sort alphabetically; they sort in declaration order.
• Adding an enum value in a migration transaction and using it in the same transaction.
• Writing addr.city instead of (addr).city in a SELECT and getting a "missing FROM-clause entry" error.
• Using a composite column where the fields need their own indexes, constraints or foreign keys; plain columns are easier to work with.

LimitsWhen not to use it

Skip enums when the set of values changes often, when values need extra attributes, or when other databases or tools must read the schema without PostgreSQL-specific types. Skip composite columns in tables for anything you query or index by field; they are best for function results and transient values.

SecuritySecurity notes

Domains are a cheap layer of input validation that no application path can bypass, which helps against bad data from new services, imports and manual fixes. Remember that CHECK only validates shape; it does not make an email address real or a value safe to display, so still encode output in the application.

ChallengeTry it yourself

BEGIN;
CREATE DOMAIN percent AS numeric(4,1) CHECK (VALUE BETWEEN 0 AND 100);
CREATE TABLE discount (code text PRIMARY KEY, pct percent NOT NULL);
INSERT INTO discount VALUES ('AUTUMN', 42.5);
SELECT code, pct FROM discount;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Data types
• Type conversion