Arrays and Range Types

A check constraint at the application layer is unreliable. Make double-booking a room structurally impossible at the database level instead.

ARRAY columns store a genuine list of values per row, queryable with operators purpose-built for set-like questions: @> asks whether one array contains another as a subset, && asks whether two arrays share at least one element. Both can be backed by a GIN index, the identical indexing mechanism already proven for full-text search and JSONB earlier in this certificate, now indexing array membership instead.\n\nRange types solve a different, equally common problem: representing an interval — a booking's start and end, a validity period — as one genuine value rather than two separate columns compared awkwardly against each other. tstzrange stores exactly that. The real payoff arrives with EXCLUDE USING GiST: a constraint that can express "no two rows may exist where this column is equal AND that range column overlaps," enforced by the database itself on every insert and update, with no application-level check-then-insert race condition possible — two nearly-simultaneous booking attempts cannot both slip through, because the database checks and rejects the conflict as the same atomic operation as the insert.

Array Operators: Contains and Overlap

SELECT id FROM sql_articles WHERE tags @> ARRAY['postgres'];        -- id 1, 3
SELECT id FROM sql_articles WHERE tags && ARRAY['mysql','performance']; -- id 2, 3

@> requires every element on the right to be present in the row's array; && only requires at least one shared element between the two arrays — genuinely different questions, both real set operations.

GIN, Genuinely Used for Array Membership at Scale

CREATE INDEX ON sql_articles_big USING GIN(tags);
EXPLAIN SELECT id FROM sql_articles_big WHERE tags @> ARRAY['postgres'];

Bitmap Heap Scan on sql_articles_big  (cost=90.42..746.15 rows=12458 width=4)
  Recheck Cond: (tags @> '{postgres}'::text[])
  ->  Bitmap Index Scan on sql_articles_big_tags_idx

The identical GIN mechanism proven for full-text search and JSONB earlier in this certificate, now backing array containment.

A Range Type and an Exclusion Constraint

CREATE TABLE room_bookings (id serial PRIMARY KEY, room_id int, booked_during tstzrange);
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE room_bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING GiST (room_id WITH =, booked_during WITH &&);

booked_during is one column, one real interval value — not a separate start and end column compared manually. btree_gist is required specifically because room_id is a plain integer, and GiST needs an operator class for the equality check on it, which the base install does not provide for simple types by default.

The Constraint, Genuinely Rejecting a Real Conflict

INSERT INTO room_bookings (room_id, booked_during) VALUES (1, tstzrange('2025-06-01 09:00', '2025-06-01 10:00'));
-- INSERT 0 1

INSERT INTO room_bookings (room_id, booked_during) VALUES (1, tstzrange('2025-06-01 09:30', '2025-06-01 10:30'));
-- ERROR:  conflicting key value violates exclusion constraint "no_overlap"
-- DETAIL:  Key (room_id, booked_during)=(1, [...)) conflicts with existing key (room_id, booked_during)=(1, [...)).

INSERT INTO room_bookings (room_id, booked_during) VALUES (2, tstzrange('2025-06-01 09:30', '2025-06-01 10:30'));
-- INSERT 0 1

The overlapping 9:30–10:30 booking for room 1 is genuinely rejected — a real constraint violation, not an application-level check that could theoretically be raced. The identical overlapping time for room 2 succeeds without issue, since the constraint only applies within the same room_id.

EXCLUDE USING GiST

A table constraint that generalizes UNIQUE beyond plain equality: EXCLUDE USING GiST (col1 WITH =, col2 WITH &&) rejects any new or updated row where col1 equals an existing row's value AND col2 overlaps that same existing row's range — enforced atomically by the database on every insert and update, with no possible race condition between a check and the write it is supposed to guard.

tstzrange

A range type representing a span of timestamp-with-timezone values as a single value, rather than two separate start/end columns. Range types support meaningful operators directly — && for overlap, @> for containment of a point or another range — which is exactly what an exclusion constraint on booking conflicts, or any interval-overlap query, needs.

🏷️ Query Arrays With Contains and Overlap

Run a containment query and an overlap query against the same array column, and see how they genuinely differ.

psql -U postgres -d beer_db -c "SELECT id FROM sql_articles WHERE tags @> ARRAY['postgres'];" -c "SELECT id FROM sql_articles WHERE tags && ARRAY['mysql','performance'];"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT id FROM sql_articles WHERE tags @> ARRAY['postgres'];" -c "SELECT id FROM sql_articles WHERE tags && ARRAY['mysql','performance'];" SET id ---- 1 3 (2 rows) id ---- 2 3 (2 rows)

⚡ Index Array Membership With GIN, at Scale

Build a larger array-tagged table, add a GIN index, and confirm a containment query genuinely uses it.

psql -U postgres -d beer_db -c "CREATE TABLE sql_articles_big (id int PRIMARY KEY, tags text[]);" -c "INSERT INTO sql_articles_big SELECT g, ARRAY[(ARRAY['postgres','mysql','sqlite','oracle'])[1+(g%4)], (ARRAY['sql','performance','indexing','replication'])[1+(g%4)]] FROM generate_series(1,50000) g;" -c "CREATE INDEX ON sql_articles_big USING GIN(tags);" -c "ANALYZE sql_articles_big;"

psql -U postgres -d beer_db -c "EXPLAIN SELECT id FROM sql_articles_big WHERE tags @> ARRAY['postgres'];"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_articles_big (id int PRIMARY KEY, tags text[]);" -c "INSERT INTO sql_articles_big SELECT g, ARRAY[(ARRAY['postgres','mysql','sqlite','oracle'])[1+(g%4)], (ARRAY['sql','performance','indexing','replication'])[1+(g%4)]] FROM generate_series(1,50000) g;" -c "CREATE INDEX ON sql_articles_big USING GIN(tags);" -c "ANALYZE sql_articles_big;" SET CREATE TABLE INSERT 0 50000 CREATE INDEX ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT id FROM sql_articles_big WHERE tags @> ARRAY['postgres'];" SET QUERY PLAN ------------------------------------------------------------------------------------------- Bitmap Heap Scan on sql_articles_big (cost=90.42..746.15 rows=12458 width=4) Recheck Cond: (tags @> '{postgres}'::text[]) -> Bitmap Index Scan on sql_articles_big_tags_idx (cost=0.00..87.31 rows=12458 width=0) Index Cond: (tags @> '{postgres}'::text[]) (4 rows)

🔒 Build an Exclusion Constraint on Room and Time Range

Confirm the room_bookings table and its EXCLUDE USING GiST constraint, already built in this lab's setup.

psql -U postgres -d beer_db -c "\\d room_bookings"

💥 Attempt a Real Double-Booking and Watch It Fail

Book room 1, then attempt an overlapping booking for the same room, then confirm a different room at the same time succeeds.

psql -U postgres -d beer_db -c "INSERT INTO room_bookings (room_id, booked_during) VALUES (1, tstzrange('2025-06-01 09:00', '2025-06-01 10:00'));"

psql -U postgres -d beer_db -c "INSERT INTO room_bookings (room_id, booked_during) VALUES (1, tstzrange('2025-06-01 09:30', '2025-06-01 10:30'));"

psql -U postgres -d beer_db -c "INSERT INTO room_bookings (room_id, booked_during) VALUES (2, tstzrange('2025-06-01 09:30', '2025-06-01 10:30'));"

student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO room_bookings (room_id, booked_during) VALUES (1, tstzrange('2025-06-01 09:00', '2025-06-01 10:00'));" SET INSERT 0 1 student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO room_bookings (room_id, booked_during) VALUES (1, tstzrange('2025-06-01 09:30', '2025-06-01 10:30'));" SET ERROR: conflicting key value violates exclusion constraint "no_overlap" DETAIL: Key (room_id, booked_during)=(1, ["2025-06-01 09:30:00+00","2025-06-01 10:30:00+00")) conflicts with existing key (room_id, booked_during)=(1, ["2025-06-01 09:00:00+00","2025-06-01 10:00:00+00")). student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO room_bookings (room_id, booked_during) VALUES (2, tstzrange('2025-06-01 09:30', '2025-06-01 10:30'));" SET INSERT 0 1

Lab 3.3.5 complete. Arrays, range types, and a constraint that makes an entire bug category impossible:\n\n\n Array @> and && operators : ✅ genuinely different results, both real\n GIN array index, used at scale : ✅ Bitmap Heap Scan, 50,000 rows\n EXCLUDE USING GiST constraint built : ✅ real, active on the table\n Double-booking genuinely rejected : ✅ real error, exact conflict named\n

Enable JavaScript to run the live terminal and track your progress.