Using variables in simple PostgresSQL queries

Question

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.

What client application or programming library do you use to run the query? — Bergi
– Bergi, Commented Dec 11, 2020 at 11:37
plain simple sql, but I found a solution now by experimenting — Kimses
– Kimses, Commented Dec 11, 2020 at 11:45

Kimses · Accepted Answer · 2020-12-11 11:50:36Z

1

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);

answered Dec 11, 2020 at 11:50

Kimses

8041 gold badge8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jim Jones Over a year ago

alternatively you can use WITH varname ( VALUES ('value1'),('value2'),...) .... If you're interested, I can add another answer

Kimses Over a year ago

@JimJones I tried this, but this did not work out

Jim Jones Over a year ago

I just added an answer to make my point clearer

Kimses Over a year ago

@JimJones ok, it turned out that I missed the correct syntax. yields the same result.

Jim Jones Over a year ago

It indeed does. The only difference is that using this other approach you avoid the multiple subselects and you're able to have multiple values inside your variable. +1 and cheers

Jim Jones · Accepted Answer · 2020-12-11 12:04:55Z

0

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

answered Dec 11, 2020 at 12:04

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

Using variables in simple PostgresSQL queries

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related