Select values that exists in array, but do not exits in database?

Question

I have got DB with IDs: 1 2 3 4 5. I need to return elements that exists in my array (simple list of data that usually specifying in IN ( ... ) ), but DO NOT exits in DB. For example checking values: 1, 2, 3, 4, 5, 6, 7.

So query should return 6, 7. How can I do it's with PostgreSQL?

Answer 1

This can be solved using except

select *
from unnest(array[1,2,3,4,5,6]) as t(id)
except
select id
from the_table

With some test data:

select *
from unnest(array[1,2,3,4,5,6]) as t(id)
except
select id
from (values (1), (2), (3), (4) ) as the_table(id)

returns

id
--
 5
 6