Range queries are a very common task in SQL: selecting dates, numbers, or even text values that fall within some specified range. For instance: all loan applications for the month of March, or all sales transactions between $50 and $500. SQL developers are generally adept at writing such queries and indexing them to ensure they perform well.

But when the requirements are changed slightly — to compare two ranges of values to find where they overlap — it’s suddenly a whole new ballgame. These so-called “overlap queries” are trickier than they appear.

In this multi-part series, I’ll explain everything you need to know about overlap queries in PostgreSQL.

Lee Asher in PostgreSQL

PostgreSQL Range Overlap Queries: GiST Indexes, EXCLUDE Constraints for Non-Overlapping Rows, and tstzrange Semantics

PostgreSQL range types and overlap queries – GiST indexes for efficient range overlap detection, EXCLUDE constraints preventing overlapping rows in…

29 November 2024 11 min read 32

Lee Asher in PostgreSQL

Solving the Overlap Query Problem in PostgreSQL

Range queries are a very common task in SQL: selecting dates, numbers, or even text values that fall within some…

03 September 2024 10 min read 431