'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
is expected to be less than or equal to the
Y value, otherwise the
is empty and the query returns an empty recordset.
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
Y come from
the request parameters, the code may look like:
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
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
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])
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.