BETWEEN SYMMETRIC operator in PostgreSQL

This post is about a nice but rarely used PostgreSQL features — BETWEEN SYMMETRIC comparison operator.

value BETWEEN X AND Y operator 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 * 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.