PostgreSQL Range Types: Date and Number Ranges

Tip: Range Column

CREATE TABLE rooms (
    id SERIAL PRIMARY KEY,
    availability daterange
);

INSERT INTO rooms VALUES (1, '[2024-01-01, 2024-12-31]');

Gotcha: Range Operators

-- Overlap check
SELECT * FROM rooms WHERE availability && '[2024-06-01, 2024-06-15]';

&& = overlap, @> = contains, <@ = contained by.

Tip: Exclude Overlapping Ranges

ALTER TABLE rooms ADD EXCLUDE USING gist (availability WITH &&);

Prevents overlapping reservations at the database level.

Gotcha: Range Bounds

[ = inclusive, ( = exclusive:

'[2024-01-01, 2024-12-31)' -- Includes Jan 1, excludes Dec 31

Tip: int4range for Number Ranges

SELECT * FROM products WHERE price_range @> 50;

Finds products where 50 is within the price range.

Gotcha: GiST Index Required

Range exclusion constraints need a GiST index. B-tree indexes don't work.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL range types are one of the most underused features in web applications. I've used daterange for booking systems (check if a date range overlaps with existing bookings), tsrange for session management, and int4range for IP range matching. The && (overlaps) operator with a GiST index makes range queries extremely efficient. This is one feature where PostgreSQL has a genuine advantage over MySQL.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)