CODEWITHSUNDEEP
postgresql
Dmitry Bubnenkov
Dmitry Bubnenkov

Reputation: 9869

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

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?

Upvotes: 4

Views: 1285

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125544

with t (id) as (values (1),(2),(3),(4),(5))
select u.id
from
    t
    right join
    unnest(array[1,2,3,4,5,6,7]) u (id) on t.id = u.id
where t.id is null
;
 id 
----
  6
  7

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 7

Mathjoh
Mathjoh

Reputation: 104

If you want a query that excludes all elements in a list you can use the NOT IN statement.

SELECT * FROM someTable WHERE id NOT IN (1, 2, 3, 4, 5);

In your case you can create the query from your array.

Upvotes: -1

Related Questions