'BETWEEN SYMMETRIC' construct in PostgreSQL

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:

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

Alternatively, we can explicitly use BETWEEN SYMMETRIC in the where condition to have the arguments automatically swapped if the range is empty. In this case the Ruby code looks like:

User.where("id BETWEEN SYMMETRIC ? AND ?", params[:min], params[:max])  

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

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

which returns records with ids equal to 2, 3 and 4.

Even though this code uses an SQL fragment in Ruby code, it is shorter, easier to understand and does not reimplement the built-in DB functionality.