'BETWEEN SYMMETRIC' construct in PostgreSQL

2016-03-20

This post is the first in the series of posts about a few handy but rarely used PostgreSQL features.
It deals with very basic stuff — BETWEEN SYMMETRIC comparison construct.

value BETWEEN X AND Y construct is familiar to most if not all developers who have ever used SQL.
It is an equivalent to value >= X AND value <= Y. For example, query
SELECT * FROM users WHERE id BETWEEN 2 AND 4 returns records with ids equal to 2, 3 and 4.

In a query like SELECT * FROM users WHERE id BETWEEN X AND Y the X value
is expected to be less than or equal to the Y value, otherwise the X..Y range
is empty and the query returns an empty recordset.
For example: SELECT * FROM users WHERE id BETWEEN 4 AND 2 returns nothing even if the table
contains rows with ids 2, 3 and 4.

This maybe inconvenient when a query is built in an application code using the range boundaries provided
by an end-user. For example, in a Ruby on Rails app, where X and Y come from
the request parameters, the code may look like:

User.where(id: params[:min]..params[:max])

If params[:min] is 2 and params[:max] is 4 then this code is converted into
this SQL query:

SELECT COUNT(*) FROM "users" WHERE ("users"."id" BETWEEN 2 AND 4)

To handle the case when params[:min] is greater than params[:max] gracefully,
the provided values should be automatically swapped. For example: