Using variables in simple PostgresSQL queries

user_9876604 0
Viewed 36

I have to perfrom a lot of queries where the same value is being reused. I thought of something like:

varName = 'value';
select * from sometable t where
 t.field1 = varName 
 or t.field2 = varName;

How can this be done with PostgreSQL 12?

I tried a lot of stuff I found, but nothing seems to work.

postgresql
2 Answers

I found a solution meanwhile

with varName as (select 'value'::text) 
select * from sometable t where
 t.field1 = (select * from varName)
 or t.field2 = (select * from varName);
user_98766040

Alternatively you can use VALUES inside of your CTE. This will enable you to have multiple values in the same "variable".

Data Sample:

CREATE TABLE t (c1 int, c2 text);
INSERT INTO t VALUES (42,'foo'),(1,'xpto');

Query

WITH j (var) AS (VALUES ('foo'),('bar')) 
SELECT t.c1,j.var FROM t
JOIN j ON j.var = t.c2;

 c1 | var 
----+-----
 42 | foo
user_22753880
Related