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

Question

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 1

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