How to use the IN clause for multiple columns

Question

I am using Postgres and I would like to put together the following sql queries

select * from t1 where type=57 and idt=2001 and date=1;
select * from t1 where type=59 and idt=2001 and date=1;
select * from t1 where type=57 and idt=2002 and date=3;
select * from t1 where type=57 and idt=2001 and date=2;
select * from t1 where type=59 and idt=2002 and date=1;

together in only one statement, like this:

select * from t1 where (type, idt, date) in 
{
(57, 2001, 1),
(59, 2001, 1),
(57, 2002, 3),
(57, 2001, 2),
(59, 2002, 1),
}

but I get the syntax error.

What's the correct syntax to achieve it?

Switch `{` to `(`, and see shat happens. And remove the last comma. — jarlh, Sep 01 '22 at 13:06

score 3 · Accepted Answer · 2022-09-01T13:14:15.567

3

Where did you find, that curly braces would be correct in this case? Just use regular parentheses:

select * 
from t1 
where (type, idt, date) in (
                             (57, 2001, 1),
                             (59, 2001, 1),
                             (57, 2002, 3),
                             (57, 2001, 2),
                             (59, 2002, 1)
                           )

edited Sep 01 '22 at 13:14

answered Sep 01 '22 at 13:09

1

cool I didn't know we could do this directly with in. Slight correction, there is an extra comma at the end. – Cetin Basoz Sep 01 '22 at 13:12
@CetinBasoz: the comma was a copy & paste error, thanks – Sep 01 '22 at 13:14

score 0 · Answer 2 · answered Sep 01 '22 at 13:06

You can join a table created with values clause, ie:

select t1.* from t1 inner join 
(values (57, 2001, 1),
(59, 2001, 1),
(57, 2002, 3),
(57, 2001, 2),
(59, 2002, 1)) as foo(type, idt, date)
on t1.type = foo.type and t1.idt = foo.idt and t1.date = foo.date;

DBFiddle demo

How to use the IN clause for multiple columns

2 Answers2