{"id":111001,"date":"2026-05-26T08:20:41","date_gmt":"2026-05-26T08:20:41","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?post_type=collection&p=111001"},"modified":"2026-05-26T08:23:58","modified_gmt":"2026-05-26T08:23:58","slug":"overlap-queries-in-postgresql-a-complete-guide","status":"publish","type":"collection","link":"https:\/\/www.red-gate.com\/simple-talk\/collections\/overlap-queries-in-postgresql-a-complete-guide\/","title":{"rendered":"Overlap queries in PostgreSQL: a complete guide"},"content":{"rendered":"\n

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.<\/strong><\/p>\n\n\n\n

But when the requirements are changed slightly — to compare two <\/em>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. <\/strong><\/p>\n\n\n\n

In this multi-part series, I’ll explain everything you need to know about overlap queries in PostgreSQL.<\/strong><\/p>\n\n\n\n

\n
\n
\n
\n
\n \n\t\"\"\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Lee Asher<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n PostgreSQL Range Overlap Queries: GiST Indexes, EXCLUDE Constraints for Non-Overlapping Rows, and tstzrange Semantics <\/h1>\n

\n PostgreSQL range types and overlap queries – GiST indexes for efficient range overlap detection, EXCLUDE constraints preventing overlapping rows in… <\/p>\n <\/div>\n

\n
\n
\n
\n \n\t\"\"\t<\/a> <\/div>\n
\n \n
\n \n \n \"Image\n Lee Asher<\/span>\n <\/a>\n\n \n in<\/span>\n \n PostgreSQL <\/a>\n <\/span>\n<\/div>\n\n
\n

\n Solving the Overlap Query Problem in PostgreSQL <\/h1>\n

\n Range queries are a very common task in SQL: selecting dates, numbers, or even text values that fall within some… <\/p>\n <\/div>\n

\n