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
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
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])
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.