Arrays, Enums & Domains

An ENUM type is a fixed, ordered list of allowed values — 'draft' < 'published' < 'archived'. It documents the options in the schema and rejects anything else. Adding values later is easy; removing them is not, so use enums for truly stable sets.

A DOMAIN is a reusable type with built-in rules: define tz_phone once (text that must match a Tanzanian mobile number pattern) and use it in every table that stores phone numbers.

Array columns (text[], smallint[]) hold several values in one field. Query them with ANY, containment @>, overlap &&, unnest (one row per element) and array_agg (rows back into an array). Arrays suit small tag lists; if items need their own attributes or foreign keys, use a separate table instead.

types.sqlSQL

CREATE TYPE notice_status AS ENUM ('draft', 'published', 'archived');
CREATE DOMAIN tz_phone AS text CHECK (VALUE ~ '^0[67][0-9]{8}$');

CREATE TABLE notices (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title     text          NOT NULL,
    status    notice_status NOT NULL DEFAULT 'draft',
    forms     smallint[]    NOT NULL DEFAULT '{}',     -- which forms should see it
    tags      text[]        NOT NULL DEFAULT '{}',
    contact   tz_phone
);

INSERT INTO notices (title, status, forms, tags, contact) VALUES
    ('Mock exams timetable', 'published', '{3,4}',     '{exams,timetable}', '0712345678'),
    ('Sports day',           'published', '{1,2,3,4}', '{sports,events}',   '0754000002'),
    ('Fees reminder',        'draft',     '{1,2,3,4}', '{fees,exams}',      NULL),
    ('Old holiday notice',   'archived',  '{1,2}',     '{holidays,events}', NULL);

-- Rejected by the enum and the domain:
INSERT INTO notices (title, status) VALUES ('Bad status', 'deleted');
INSERT INTO notices (title, contact) VALUES ('Bad phone', '12345');

-- Enums are ordered
SELECT title, status FROM notices WHERE status < 'archived' ORDER BY status, title;

-- Arrays: notices for Form 4, with an exams or fees tag
SELECT title FROM notices
WHERE 4 = ANY (forms) AND tags && '{exams,fees}'
ORDER BY title;

-- unnest: one row per tag, then count tag usage
SELECT tag, count(*) AS notices
FROM notices, unnest(tags) AS tag
GROUP BY tag
ORDER BY notices DESC, tag;

-- array_agg: back to one array per status
SELECT status, array_agg(title ORDER BY title) AS titles
FROM notices
GROUP BY status
ORDER BY status;

CREATE INDEX idx_notices_tags ON notices USING gin (tags);
SELECT enum_range(NULL::notice_status) AS allowed_statuses;

Runs in your browser · PostgreSQL

Key points

Enums fix a set of ordered values; domains bundle a type with reusable rules.
Query arrays with ANY, @>, &&; turn them into rows with unnest, back with array_agg.
Use arrays for small value lists; use a related table when items need their own data or keys.

Exercise

Create an enum term_name ('term1', 'term2', 'term3') and a domain score (smallint between 0 and 100). Create a term_scores table using both plus a remarks text[] column, insert a few rows, and find every row whose remarks contain 'improved'.

Show solution

Try the exercise yourself first — then compare your approach with this one.

The enum fixes the term names, the domain carries the 0–100 rule to any table that uses it, and 'improved' = ANY (remarks) searches inside the array.

term-scores.sqlSQL

CREATE TYPE term_name AS ENUM ('term1', 'term2', 'term3');
CREATE DOMAIN score AS smallint CHECK (VALUE BETWEEN 0 AND 100);

CREATE TABLE term_scores (
    student_id bigint    NOT NULL REFERENCES students (id),
    term       term_name NOT NULL,
    value      score     NOT NULL,
    remarks    text[]    NOT NULL DEFAULT '{}',
    PRIMARY KEY (student_id, term)
);

INSERT INTO term_scores VALUES
    (1, 'term1', 86, '{consistent}'),
    (1, 'term2', 89, '{improved,consistent}'),
    (4, 'term1', 39, '{needs support}'),
    (4, 'term2', 47, '{improved}');

INSERT INTO term_scores VALUES (2, 'term1', 120, '{}');   -- rejected by the domain

SELECT s.full_name, t.term, t.value
FROM term_scores t
JOIN students s ON s.id = t.student_id
WHERE 'improved' = ANY (t.remarks)
ORDER BY s.full_name, t.term;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding