PostgreSQL has a richer type system than many relational databases. Beyond scalar columns such as integer, text, and timestamp, it can store lists, bounded intervals, named states, constrained values, and structured row-like values. The purpose is not to make tables less relational. The purpose is to let the database represent a domain concept with the right shape and reject invalid values close to the data.
In this chapter, the outcome is practical: you should be able to decide when an array, range, enum, domain, or composite type improves a PostgreSQL model, write the core syntax, index or constrain the values when appropriate, and diagnose the common failures these types introduce. This belongs in the advanced data features section because these types affect schema design, query operators, indexes, migrations, and application contracts.
What Each Type Is For
An array stores multiple values of the same PostgreSQL type in one column, such as text[] or integer[]. Arrays are useful for small, bounded collections that are usually read and written with the parent row: tags, selected weekdays, or a short ordered list. They are not a replacement for child tables when each element needs its own identity, permissions, timestamps, foreign keys, or frequent independent updates.
A range stores a lower bound, an upper bound, and bound inclusivity for a continuous span such as time, dates, integers, or numbers. Built-in range types include int4range, numrange, daterange, tsrange, and tstzrange. Ranges understand overlap, containment, adjacency, emptiness, and canonical forms, so they are much safer than storing separate start and end columns and reimplementing interval logic in every query.
An enum defines a fixed ordered set of labels. It is appropriate when the allowed values are few, stable, and part of the database contract, such as a booking state. Enums are compact and self-documenting, but they are deliberately harder to change than lookup table rows.
A domain wraps an existing type with constraints and optional defaults. For example, a positive_cents domain can be based on integer and reject negative values everywhere it is used. Domains are a good way to reuse a value-level rule without copying the same CHECK constraint into many tables.
A composite type is a named structure with fields, similar to a row type. Tables automatically create composite row types, and you can also define your own. Composite types are useful for passing structured values to functions or storing a small structured value that belongs to exactly one row. They do not replace normalized tables when the nested value needs to be queried, joined, or constrained independently at scale.
How PostgreSQL Handles Them Internally
Arrays are variable-length values. PostgreSQL records the element type, dimensions, optional lower bounds, and the element values. Most arrays use one-based indexing by convention, so tags[1] returns the first element. Arrays can be multi-dimensional, but multi-dimensional arrays are easier to misuse because every nested dimension must remain rectangular. For most application schemas, one-dimensional arrays are the maintainable choice.
Range values contain two endpoints plus flags that describe whether each endpoint is inclusive, exclusive, infinite, or missing. A range can also be empty. The text literal [) means inclusive lower bound and exclusive upper bound, a common convention for time reservations because one booking ending at 11:00 does not conflict with another beginning at 11:00. Range operators such as && for overlap and @> for contains use those bound rules consistently.
Enums are stored as typed labels with an internal sort order. Comparisons follow enum order, not alphabetical order. PostgreSQL allows adding enum labels, but removing labels or freely reordering them is intentionally awkward because existing rows may depend on them. If business users need to manage the list themselves, a lookup table with a foreign key is usually better.
Domains apply constraints when a value is cast to the domain or assigned to a domain-typed column. The special identifier VALUE refers to the candidate value inside the domain check. A domain can be nullable unless the domain itself declares NOT NULL, and table columns using that domain can still add their own column-level constraints.
Composite values behave like typed records. Field access uses parentheses around the composite column, such as (charges).tax, because without parentheses the parser treats the name as a table or alias qualifier. PostgreSQL can compare, pass, and store composite values, but indexing individual fields usually requires expression indexes or a more normalized design.
Syntax Anatomy
Arrays use the element type followed by square brackets: text[], uuid[], or integer[]. Array literals can be written with ARRAY[...]. Common operators include @> for contains, <@ for is contained by, and && for overlaps. A GIN index is often the right index for containment or overlap searches on arrays.
Ranges are constructed with functions such as tstzrange(lower, upper, bounds). Common operators include && for overlap, @> for contains a value or range, << for strictly left of, and -|- for adjacent. GiST indexes support range searches and exclusion constraints.
Enums use CREATE TYPE name AS ENUM (...). Domains use CREATE DOMAIN name AS base_type CHECK (...). Composite types use CREATE TYPE name AS (...), and values can be built with ROW(... )::type_name.
Example 1: Define a Booking Model
The first example creates all five advanced type categories in one small room-booking schema. The exclusion constraint prevents two rows for the same room from having overlapping reservation ranges. The btree_gist extension lets GiST compare the text room value inside the same exclusion constraint as the range.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TYPE ticket_state AS ENUM ('draft', 'confirmed', 'cancelled');
CREATE DOMAIN positive_cents AS integer
CHECK (VALUE >= 0);
CREATE TYPE money_breakdown AS (
subtotal positive_cents,
tax positive_cents
);
CREATE TABLE workshop_booking (
booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room text NOT NULL,
attendee_email text NOT NULL,
tags text[] NOT NULL DEFAULT ARRAY[]::text[],
reserved tstzrange NOT NULL,
state ticket_state NOT NULL DEFAULT 'draft',
charges money_breakdown NOT NULL,
CHECK (array_position(tags, NULL) IS NULL),
CHECK (NOT isempty(reserved)),
EXCLUDE USING gist (room WITH =, reserved WITH &&)
);The deterministic behavior is that the table accepts non-empty reservations, non-null tag elements, valid enum states, and non-negative money fields. It rejects overlapping reservations in the same room because the exclusion constraint compares both room and reserved. It permits overlapping times in different rooms because the room comparison is part of the rule.
Example 2: Arrays for Small Collections
Now insert two non-overlapping bookings and query by tag. The tags are stored with the booking because they are short descriptive labels, not independent entities with their own lifecycle.
INSERT INTO workshop_booking
(room, attendee_email, tags, reserved, state, charges)
VALUES
('blue', 'a@example.test', ARRAY['postgresql', 'arrays'],
tstzrange('2026-09-07 09:00+00', '2026-09-07 10:00+00', '[)'),
'confirmed', ROW(12000, 960)::money_breakdown),
('blue', 'b@example.test', ARRAY['postgresql', 'ranges', 'hybrid'],
tstzrange('2026-09-07 10:00+00', '2026-09-07 11:00+00', '[)'),
'draft', ROW(8000, 640)::money_breakdown);
SELECT booking_id,
tags[1] AS first_tag,
tags @> ARRAY['hybrid']::text[] AS has_hybrid_tag,
tags || ARRAY['recorded']::text[] AS displayed_tags
FROM workshop_booking
ORDER BY booking_id;The result has two rows. The first row has first_tag equal to postgresql, has_hybrid_tag equal to false, and displayed_tags with recorded appended. The second row has has_hybrid_tag equal to true. Notice that array concatenation in the query does not update the stored column; it only computes a result expression.
The design trade-off is density versus relational clarity. An array is compact and convenient when the whole list belongs to the row. A join table is better when you need uniqueness across rows, per-tag metadata, foreign keys to a controlled vocabulary, or analytics over large tag sets. If you query tags @> ... often, add a GIN index and test it with realistic row counts.
Example 3: Ranges for Time Logic
Time intervals are a natural fit for ranges because the database understands boundary semantics. This query asks which bookings overlap a requested slot and whether a specific instant is inside each reservation.
SELECT booking_id,
room,
lower(reserved) AS starts_at,
upper(reserved) AS ends_at,
reserved && tstzrange('2026-09-07 09:30+00', '2026-09-07 10:30+00', '[)') AS overlaps_request,
reserved @> '2026-09-07 10:00+00'::timestamptz AS contains_ten
FROM workshop_booking
ORDER BY booking_id;Both rows overlap the requested 09:30 to 10:30 slot. The first row does not contain exactly 10:00 because its range is inclusive at 09:00 and exclusive at 10:00. The second row does contain 10:00 because 10:00 is its inclusive lower bound. This is the key reason to prefer ranges over hand-written start_at < requested_end AND end_at > requested_start expressions scattered through an application.
For reliability, pair range columns with exclusion constraints when overlap is invalid. Without the constraint, two application processes can both check that a room is free and then both insert conflicting rows. The database constraint turns that race into one successful transaction and one rejected transaction.
Example 4: Enums, Domains, and Composite Fields
This example reads fields from the composite value, changes an enum state, and uses domain-backed fields in arithmetic. The domain keeps negative money out of the stored composite value.
UPDATE workshop_booking
SET state = 'confirmed'
WHERE attendee_email = 'b@example.test';
SELECT booking_id,
state,
(charges).subtotal AS subtotal_cents,
(charges).tax AS tax_cents,
(charges).subtotal + (charges).tax AS total_cents
FROM workshop_booking
ORDER BY booking_id;The first booking remains confirmed. The second booking changes from draft to confirmed. The totals are 12960 and 8640 cents. The parentheses around charges are not cosmetic; they tell PostgreSQL to access fields of the composite column rather than parse charges.subtotal as a qualified name.
The trade-off for enums is migration rigidity. They are excellent for states controlled by the development team, but poor for user-managed lists. Domains are usually lower risk because they wrap ordinary base types, but changing a domain constraint can require checking existing data. Composite columns keep related fields together, but if you constantly filter by (charges).tax, that field probably deserves its own column or an expression index.
Failure Modes and Troubleshooting
Symptom: inserting a back-to-back reservation unexpectedly fails. Cause: the range used inclusive upper bounds, such as [], so the first reservation includes its endpoint. Diagnose: select lower(reserved), upper(reserved), and the textual range value to inspect bounds. Correct: use [) for scheduling intervals unless your domain explicitly needs inclusive ends.
Symptom: a query using tags @> ARRAY['hybrid'] scans the whole table. Cause: there is no suitable GIN index, the table is too small for the planner to prefer it, or statistics do not reflect current data. Diagnose: run EXPLAIN on the query and check whether a sequential scan is chosen. Correct: create a GIN index on the array column when the workload justifies it, then run ANALYZE and retest with representative volume.
Symptom: changing an enum is easy to add but hard to undo. Cause: enum labels are part of the type definition and may be stored in rows. Diagnose: inspect dependencies with catalog queries or migration tooling before changing the type. Correct: for volatile business lists, use a lookup table and foreign key instead of an enum.
Symptom: a composite field reference fails with a missing table or column error. Cause: field access was written without parentheses. Diagnose: compare charges.subtotal with (charges).subtotal. Correct: parenthesize composite columns when selecting nested fields.
Security, Performance, and Reliability
Advanced types can strengthen security and reliability because invalid states are refused inside the database. Domains prevent repeated validation drift. Enums prevent misspelled states. Ranges plus exclusion constraints prevent booking races. These benefits apply only when the type encodes a real invariant rather than a convenience that hides a missing table.
Performance depends on operators and indexes. Arrays commonly use GIN indexes for containment and overlap. Ranges commonly use GiST indexes for overlap and exclusion constraints. Composite fields do not automatically become independently indexed search fields. If a value becomes a major filter, join key, or aggregation dimension, promote it to a normal column or related table.
Hands-On Lab
Prerequisites: use a PostgreSQL database where you can create types, tables, and the btree_gist extension. Run the lab in a scratch schema or disposable database.
- Create the extension, enum, domain, composite type, and
workshop_bookingtable from Example 1. - Run the inserts and array query from Example 2. Verify that the second row reports
has_hybrid_tagas true. - Run the range query from Example 3. Verify that exactly 10:00 is not contained by the first reservation but is contained by the second.
- Attempt, in your own scratch session, to insert another blue-room booking from 09:30 to 09:45. Verify that the exclusion constraint rejects the overlap.
- Run the enum and composite query from Example 4. Verify totals of 12960 and 8640 cents.
- Clean up the objects when finished.
DROP TABLE IF EXISTS workshop_booking;
DROP TYPE IF EXISTS money_breakdown;
DROP DOMAIN IF EXISTS positive_cents;
DROP TYPE IF EXISTS ticket_state;The cleanup drops the table before the dependent types. If you created extra indexes or test rows on the same table, they disappear with the table. Do not run the cleanup in a shared schema unless you intentionally created these objects there.
Assessment Exercises
- A product table has a
colors text[]column. Customers can add new color names, and reports need counts by color. Should this stay an array? Explain the schema you would choose and why. - Design a constraint that prevents overlapping equipment rentals for the same asset. Which range type and index-backed constraint would you use?
- An order status changes weekly because operations keeps adding temporary states. Should you use an enum, a domain, or a lookup table? Defend your answer in migration terms.
- A domain
positive_centsallows zero. A new business rule requires values greater than zero for one table only. Should you change the domain or add a table-level constraint? Explain the blast radius. - You need to query millions of rows by a field inside a composite column. What symptoms would tell you the composite design is now working against PostgreSQL?
Summary
Arrays, ranges, enums, domains, and composite types let PostgreSQL model richer facts than plain scalar columns. Use arrays for small row-owned lists, ranges for interval logic, enums for stable database-owned states, domains for reusable value rules, and composites for small structured values. The best designs combine these types with constraints and indexes so the database can both store the shape and enforce the rule.
