Numeric variables in psql

Question

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?

Answer 1

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;

Answer 2

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.

Example:

=# \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.