TopolearnThese PostgreSQL types model common patterns concisely, as long as you know where each one stops fitting.
Arrays: any type can be an array, e.g. text[]. Literals look like '{a,b}' or ARRAY['a','b']. Useful operators: 'x' = ANY(tags), containment tags @> ARRAY['x'] (GIN-indexable), overlap &&, array_length(tags, 1), and unnest(tags) to turn an array into rows; array_agg goes the other way. Arrays are 1-based. Good for small lists of tags or values you read together; if you need foreign keys or per-item attributes, use a child table.
Enums: CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'). Values are stored compactly and sort in declaration order. You can add values (ALTER TYPE mood ADD VALUE 'ecstatic' AFTER 'happy'), but removing or renaming is awkward. For sets that change often, a lookup table or a CHECK constraint is more flexible.
Domains: a named type with constraints, reusable across tables: CREATE DOMAIN email AS text CHECK (VALUE ~ '^[^@]+@[^@]+$'). Changing the rule in one place updates every column using it.
All three are PostgreSQL extensions to standard SQL (arrays are partly standard), so they tie you a little more to PostgreSQL — usually a fine trade.
Example
CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'closed');
CREATE DOMAIN positive_int AS int CHECK (VALUE > 0);
CREATE TABLE ticket (
id int PRIMARY KEY,
status ticket_status NOT NULL DEFAULT 'open',
priority positive_int NOT NULL,
tags text[] NOT NULL DEFAULT '{}'
);
INSERT INTO ticket VALUES (1, 'open', 2, '{bug,ui}'), (2, 'closed', 1, '{docs}');
SELECT id FROM ticket WHERE 'bug' = ANY(tags); -- 1
SELECT id, unnest(tags) AS tag FROM ticket ORDER BY id; -- 1 bug, 1 ui, 2 docs
SELECT status FROM ticket ORDER BY status; -- open, closed (enum order)
INSERT INTO ticket VALUES (3, 'open', 0, '{}');
-- ERROR: value for domain positive_int violates check constraintRemember
- Arrays: = ANY(), @>, && and unnest; 1-based
- Enums sort in declaration order and are hard to shrink
- Domains are reusable constrained types
Watch out for
- Using an array of ids instead of a junction table, losing foreign keys.