Arrays and Ranges

CREATE TABLE posts (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tags   text[]  NOT NULL DEFAULT '{}',
  scores int[]
);

INSERT INTO posts (tags, scores) VALUES
  (ARRAY['sql','postgres'], ARRAY[3,5,4]),
  ('{"multi word",plain}',  '{1,2}');

SELECT ARRAY[1,2,3][2];      -- ERROR, syntax error at "["
SELECT (ARRAY[1,2,3])[2];    -- 2

SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql','postgres'];   -- has both
SELECT * FROM posts WHERE tags && ARRAY['sql','rust'];       -- has either
SELECT * FROM posts WHERE NOT ('draft' = ANY(tags));
SELECT ARRAY[1,2] || 3, 0 || ARRAY[1,2];                     -- {1,2,3}, {0,1,2}
SELECT * FROM posts WHERE id = ANY($1::bigint[]);            -- one param, many ids

-- Rows to array, and back
SELECT customer_id, array_agg(id ORDER BY placed_at) AS order_ids
FROM orders GROUP BY customer_id;

SELECT p.id, t.tag, t.ord
FROM posts p
CROSS JOIN LATERAL unnest(p.tags) WITH ORDINALITY AS t(tag, ord);

-- Preserve incoming order when fetching by an id list
SELECT o.* FROM orders o
JOIN unnest($1::bigint[]) WITH ORDINALITY AS k(id, ord) ON k.id = o.id
ORDER BY k.ord;

CREATE INDEX posts_tags_gin ON posts USING gin (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

SELECT int4range(1, 10);                             -- [1,10)
SELECT '[1,10]'::int4range;                          -- [1,11) after canonicalization
SELECT numrange(1.5, 2.5, '[]');                     -- [1.5,2.5]
SELECT daterange('2026-01-01', '2026-02-01');        -- [2026-01-01,2026-02-01)
SELECT tstzrange(now(), now() + interval '1 hour');
SELECT tsrange(NULL, '2026-01-01');                  -- (,"2026-01-01 00:00:00")
SELECT '(,)'::int4range;                             -- unbounded both ends

SELECT '[1,10)'::int4range @> 5              AS has_five;      -- t
SELECT '[1,5)'::int4range && '[4,9)'::int4range AS overlaps;   -- t
SELECT '[1,5)'::int4range -|- '[5,9)'::int4range AS adjacent;  -- t
SELECT '[1,5)'::int4range * '[4,9)'::int4range  AS shared;     -- [4,5)
SELECT range_merge('[1,3)'::int4range, '[8,9)'::int4range);    -- [1,9)

-- Find rows whose validity window covers now
SELECT * FROM prices WHERE valid_during @> now();

-- Duration of a range
SELECT upper(during) - lower(during) AS length FROM bookings;

CREATE INDEX bookings_during_gist ON bookings USING gist (during);

SELECT '{[1,3),[8,9)}'::int4multirange;
SELECT int4multirange(int4range(1,3), int4range(8,9));
SELECT '{[1,10)}'::int4multirange - '{[3,4)}'::int4multirange;   -- {[1,3),[4,10)}
SELECT range_agg(during) FROM bookings;                          -- rows to multirange
SELECT unnest('{[1,3),[8,9)}'::int4multirange);                  -- back to rows
SELECT '{[1,3)}'::int4multirange @> 2, '{[1,3)}'::int4multirange && '[2,5)'::int4range;

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id bigint    NOT NULL REFERENCES rooms,
  during  tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

INSERT INTO bookings (room_id, during)
VALUES (1, '[2026-03-01 09:00+00, 2026-03-01 11:00+00)');   -- ok

INSERT INTO bookings (room_id, during)
VALUES (1, '[2026-03-01 10:00+00, 2026-03-01 12:00+00)');
-- ERROR:  conflicting key value violates exclusion constraint
--         "bookings_room_id_during_excl"   (SQLSTATE 23P01)

INSERT INTO bookings (room_id, during)
VALUES (1, '[2026-03-01 11:00+00, 2026-03-01 12:00+00)');   -- ok, [) means they touch

ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
  WHERE (status <> 'cancelled');