PostgreSQL check if values in a given list exist in a table

Question

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

For example where id in (1, 2, 3, 4, 5):

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!

You need to OUTER JOIN the values 1 - 5.

jarlh
– jarlh

2022-01-20 11:38:43 +00:00
Commented Jan 20, 2022 at 11:38 — jarlh
– jarlh, Commented Jan 20, 2022 at 11:38

Salman Arshad · Accepted Answer · 2022-01-20 12:34:22Z

4

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, EXISTS (
    SELECT *
    FROM t
    WHERE t.id = x.id
) AS value
FROM (VALUES (1), (2), (3), (4), (5)) AS x(id)

edited Jan 20, 2022 at 12:34

answered Jan 20, 2022 at 11:52

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

6 Comments

Forece85 Over a year ago

could you pls explain if this would count all rows for each id internally? Bcz for each id, I may how thousands of rows in my case. I want to eliminate that.

wildplasser Over a year ago

You don't need the CASE expression. EXISTS() already yields a boolean.

Forece85 Over a year ago

would limit 1 to subquery help for your answer?

Forece85 Over a year ago

Added limit 1 to the subquery of your answer. It worked. Thankyou.

Salman Arshad Over a year ago

@Forece85 no limit is not required. It is wrapped inside exists so basically it means check for presence of rows - 1 row or 1000 rows does not matter.

|

Collectives™ on Stack Overflow

PostgreSQL check if values in a given list exist in a table

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related