Arrays, Enums & Range Types

Array columns, enum types with a defined order, and ranges that answer 'do these periods overlap?'.

PostgreSQL has types that most databases lack. Arrays store a list in one column, enums define a fixed ordered set of labels, and range types represent intervals of values — dates, times, numbers — with operators for overlap and containment.

Skills, priorities and lab bookings

'Python' = ANY (skills) tests membership; skills @> ARRAY['C', 'CAN'] requires all listed values.

unnest(skills) turns each array element into a row, so normal GROUP BY counts can be used.

Enum values compare by their declared order, so prio >= 'high' means high or critical.

[2025-03-03,2025-03-07) includes the start and excludes the end; && returns ranges that overlap the query range.

When to use them

SELECT * FROM employees WHERE skills = '{Python}';

SELECT * FROM employees WHERE 'Python' = ANY (skills);

= compares whole arrays, so it only matches people whose only skill is Python.

How do you find bookings that overlap 2025-03-05 to 2025-03-08?

WHERE during && daterange('2025-03-05', '2025-03-08') When is a separate table better than an array column?

When elements need their own attributes, foreign keys or constraints, are queried individually across rows often, or the list can grow large.