Arrays, Composite Types, ENUMs, Domains, and When Custom Types Improve Modeling

Learning outcomes

Built-in types cover most columns, but PostgreSQL also lets a database define richer types. This can make a schema expressive: a constrained email-like string can be a domain, a workflow status can be an ENUM, a structured value can be composite, and a genuinely small same-row collection can be an array. The risk is overusing those features and creating a schema that is harder to evolve or query than a normalized design.

ServiceHub will use four temporary modeling problems—technician skills, contact points, work-order status, and validated percentages—to compare these mechanisms with normalized tables and JSONB. The emphasis is decision quality, not feature collecting.

1. Arrays: one value can be a collection, but that does not make every relationship an array

PostgreSQL arrays can contain values of built-in or user-defined element types and can be multidimensional. They work well when a collection is naturally part of one row, has modest cardinality, and is usually read/written as a unit. Examples include a short ordered list of calibration coefficients or a compact set of labels. They are often a poor fit for entities with their own lifecycle, attributes, foreign keys, or many-to-many relationships.

CREATE TABLE app.ch04_array_probe (    technician_code text PRIMARY KEY,    skill_tags text[] NOT NULL DEFAULT '{}',    weekly_scores integer[][]);INSERT INTO app.ch04_array_probe(technician_code, skill_tags, weekly_scores)VALUES ('TECH-01', ARRAY['pump','electrical'], ARRAY[[4,5],[3,4]]);SELECT technician_code,       skill_tags[1] AS first_skill,       cardinality(skill_tags) AS skill_count,       skill_tags @> ARRAY['pump'] AS has_pump_skillFROM app.ch04_array_probe;

Declared array dimensions in a column type are documentation-like rather than a strict dimensionality constraint. If shape matters, enforce it with a CHECK or use a normalized child table.

2. NULL array, empty array, NULL element

These are three different states and applications should not collapse them accidentally:

SELECT NULL::text[] AS unknown_array,       '{}'::text[] AS known_empty_array,       ARRAY['pump', NULL, 'electrical']::text[] AS array_with_unknown_element;SELECT cardinality(NULL::text[]) AS unknown_count,       cardinality('{}'::text[]) AS empty_count;

Array lower bounds can also differ from 1. Most application code assumes one-based arrays, so avoid exotic bounds unless you have a strong reason and tests around driver behavior.

SELECT '[0:2]={10,20,30}'::integer[] AS zero_based,       array_lower('[0:2]={10,20,30}'::integer[],1) AS lower_bound,       ('[0:2]={10,20,30}'::integer[])[0] AS first_value;

3. Normalized relationship versus array

A technician's skills look array-shaped until the business asks for skill certification date, proficiency level, expiry, verifier, and skill-level reporting. Then “skill” has become an entity/relationship and a normalized table is clearer.

4. Composite types: structured values with named fields

Every table creates an associated composite row type, and you can also create standalone composite types. A composite value can be useful for a cohesive value object that travels together. It is not a replacement for a table when the fields need independent identity, indexing, privileges, or references.

CREATE TYPE app.ch04_contact_point AS (    channel text,    value text);CREATE TABLE app.ch04_contact_probe (    technician_code text PRIMARY KEY,    primary_contact app.ch04_contact_point NOT NULL);INSERT INTO app.ch04_contact_probeVALUES ('TECH-01', ROW('email','tech01@example.invalid')::app.ch04_contact_point);SELECT technician_code,       (primary_contact).channel AS channel,       (primary_contact).value AS contact_valueFROM app.ch04_contact_probe;

Parentheses around a composite expression are often necessary to disambiguate field selection. A standalone composite type itself does not carry table constraints such as NOT NULL on each field. If validity rules matter, enforce them at the table/domain/application boundary or use a normalized table.

5. ENUM: ordered labels with deliberate evolution

An ENUM gives a fixed ordered set of labels. PostgreSQL stores the type identity and preserves the declared order, so comparisons and sorting use enum order rather than alphabetic text order.

CREATE TYPE app.ch04_work_state AS ENUM('new','assigned','in_progress','blocked','done');CREATE TABLE app.ch04_enum_probe (    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    state app.ch04_work_state NOT NULL DEFAULT 'new');INSERT INTO app.ch04_enum_probe(state)VALUES ('done'), ('new'), ('in_progress');SELECT state FROM app.ch04_enum_probe ORDER BY state;

ENUMs are intentionally constrained. PostgreSQL supports adding/renaming enum labels, but removing/reordering values is not a simple routine operation. That is useful when labels are genuinely stable schema concepts; it is friction when product administrators frequently invent, reorder, localize, or retire workflow states.

ALTER TYPE app.ch04_work_state ADD VALUE IF NOT EXISTS 'awaiting_parts' BEFORE 'done';SELECT enumlabel, enumsortorderFROM pg_catalog.pg_enumWHERE enumtypid='app.ch04_work_state'::regtypeORDER BY enumsortorder;

For frequently changing reference data, a lookup table with keys, metadata, activation flags, translations, and foreign keys can be more maintainable.

6. Domains: reuse scalar meaning plus constraints

A domain is a user-defined type built on another type with optional constraints. It is strong when the same scalar rule appears across many tables—for example a percentage bounded from 0 through 100.

CREATE DOMAIN app.ch04_percentage AS numeric(5,2)CHECK (VALUE >= 0 AND VALUE <= 100);CREATE TABLE app.ch04_domain_probe (    work_order_id bigint PRIMARY KEY,    completion app.ch04_percentage NOT NULL);INSERT INTO app.ch04_domain_probe VALUES (1, 87.50);-- Deliberately invalid:INSERT INTO app.ch04_domain_probe VALUES (2, 120);

Domain constraints are checked when a value is converted to or stored as the domain. When operators work on the value, PostgreSQL can down-cast it to its base type; an expression result does not automatically remain the domain. Cast back to the domain if you need its constraints rechecked.

SELECT pg_typeof(completion) AS stored_type,       pg_typeof(completion - 10) AS expression_type,       (completion - 10)::app.ch04_percentage AS checked_againFROM app.ch04_domain_probeWHERE work_order_id=1;

7. Custom types versus JSONB

jsonb is useful for document-like attributes whose shape legitimately varies or evolves independently, but it weakens some compile-time/schema-level guarantees compared with typed columns. A composite type is rigid and schema-visible. An array has a homogeneous element type. A domain carries a scalar contract. An ENUM carries a closed ordered vocabulary. A normalized table carries relational identity and constraints.

8. Hands-on lab: model one feature four ways

Model technician qualification with increasingly demanding requirements. Begin with simple tags, then ask what happens when each qualification gets an issuer, expiry date, evidence URI, and revocation status.

1. Create an array-based prototype containing three skill tags.
2. Query it with containment and unnest().
3. Add a composite contact value and inspect fields.
4. Create/use the workflow ENUM, then add one value in a controlled migration.
5. Create/use the percentage domain and provoke its check error.
6. Write a short design note explaining which feature should remain custom-typed and which should move to normalized tables.

SELECT technician_code, skillFROM app.ch04_array_probeCROSS JOIN LATERAL unnest(skill_tags) AS skillORDER BY technician_code, skill;SELECT technician_codeFROM app.ch04_array_probeWHERE skill_tags @> ARRAY['electrical'];

9. Cleanup for custom-type probes

Drop dependent tables before dropping their types. This is another practical connection to Chapter 03's dependency lesson.

DROP TABLE IF EXISTS app.ch04_array_probe;DROP TABLE IF EXISTS app.ch04_contact_probe;DROP TABLE IF EXISTS app.ch04_enum_probe;DROP TABLE IF EXISTS app.ch04_domain_probe;DROP TYPE IF EXISTS app.ch04_contact_point;DROP TYPE IF EXISTS app.ch04_work_state;DROP DOMAIN IF EXISTS app.ch04_percentage;

Keep app.ch04_type_lab from Lesson 1 for later Chapter 04 integration exercises.

10. Summary and bridge

PostgreSQL can encode meaningful structure directly in the type system, but every custom type becomes part of schema evolution. Arrays, composites, ENUMs, and domains are powerful when the business concept matches their semantics; normalized tables and JSONB remain important alternatives. Next, constraints will connect those typed values across columns and rows, including deferred checks and exclusion rules that are uniquely expressive in PostgreSQL.

Authoritative references

• PostgreSQL 18 — Arrays
• PostgreSQL 18 — Composite Types
• PostgreSQL 18 — Enumerated Types
• PostgreSQL 18 — Domain Types
• PostgreSQL 18 — CREATE TYPE
• PostgreSQL 18 — CREATE DOMAIN
• PostgreSQL 18 — JSON Types