Jerry Jerry - 6 months ago 14
SQL Question

SQL - return ids from input that do not exist in database

I am trying to write a query in postgresql that will do the follow:
I want to give an array of ids, look them up in my database and ONLY return the ids (from the input) if there is no row associated with that id.

example:
input:

(1,2,3,4,5)


table:

id | name
---------
1 bobby
5 michael
6 amy
10 clare


output:
2,3,4


I am been looking into CASE, using NOT IN and just regular SELECTs but I can't seem to figure this one out.

Thanks in advance!

Answer

Try this:

SELECT t1.v
FROM (VALUES (1), (2), (3), (4), (5)) t1(v)
LEFT JOIN mytable t2 ON t1.v = t2.id
WHERE t2.id IS NULL

Demo here