I am running a number of queries in psql using for a time interval where a part of the query goes like

 where g2.datetime between g1.datetime+'19 minutes' and g1.datetime+'21 minutes'

i.e. I want an interval around 20 minutes after g1.datetime

If I want to do this several times, both to make it easier and less error prone (I am also refering to 20 minutes a couple of times in the query). I intended to do something like:

\set timelag 20
\set before :timelag-1
\set after :timelag+1

if I do a

select :timelag, :before, :after;

this seems to work nicely, but obiously, I am just concatenating strings (so the query is really 'select 20, 20-1, 20+1'), because if I do try make what I need:

\set beforemin '\'' :before ' minutes\''

it turns out that what I have is '20-1 minutes' - that does not work as intended in the query.

Is there any possibility to make psql variables work as numbers or is that beyond the scope of those so I will have to define the query in some other language or as a function?


Not sure of the question, but psql doesn't have variables at all. It's just a macro language.

\set p 50
\set z 50::numeric(20,5)

SELECT pg_typeof(:p), pg_typeof(:z);
 pg_typeof | pg_typeof 
 integer   | numeric
(1 row)

\set after '''1 day''::interval'
\set before '''1 day''::interval'
SELECT 1 WHERE now() BETWEEN now() - :before AND now() + :after;

Is there any possibility to make psql variables work as numbers

Yes, by using the SQL evaluator along with \gset to copy results into the variables.


=# \set timelag 20
=# SELECT :timelag-1 AS before, :timelag+1 AS after \gset
=# \echo :before :after
19 21

With PostgreSQL 10 or better, variables are also expanded in backtick-executed strings, so we may also call an external evaluator through a shell:

=# \set before `expr :timelag - 1`
=# \set after `expr :timelag + 1`

or the more modern (with bash):

=# \set before `echo $((:timelag - 1))`
=# \set after `echo $((:timelag + 1))`

That invocation outside of SQL might be handy in the rare cases when these expressions may have to be computed in the middle of a failed transaction, or when disconnected from the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.