Postgers Exists with where in condition

January 20, 2022

Given below table in Postgres:

id	some_col
1	a
1	b
2	a
3	a

I want to get output as id and true (if at least one row with that id is present in the table) or false (if no rows with that id are found in the table).

ex:
where id in (1,2,3,4,5)
output:

id	value
1	true
2	true
3	true
4	false
5	false

I tried with the group by and case with conditions, but is there any optimized/performance SQL way of achieving this? Bcz, groupby does complete rows count which is unnecessary in this scenario. And, with exists query I didn’t find a way to deal with multiple rows return;

Thanks in Advance!

>Solution :

The in clause won’t do it. You need to build a list of values somehow (the values table valued constructor works in most rdbms) and outer join with it:

SELECT x.id, CASE WHEN EXISTS (
    SELECT *
    FROM t
    WHERE t.id = x.id
) THEN true ELSE false END AS value
FROM (VALUES (1), (2), (3), (4), (5)) AS x(id)