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?

I don't understand the output. Also, when say "array" are you using Postgres arrays or do you just generally mean a collection of numbers? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 8, 2017 at 13:43

user330315 · Accepted Answer · 2017-12-08 13:48:16Z

7

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

answered Dec 8, 2017 at 13:48

user330315

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

Comments

Clodoaldo Neto · Accepted Answer · 2017-12-08 13:48:46Z

0

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

answered Dec 8, 2017 at 13:48

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

Mathjoh · Accepted Answer · 2017-12-08 13:46:50Z

-1

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.

answered Dec 8, 2017 at 13:46

Mathjoh

1041 silver badge10 bronze badges

1 Comment

user330315 Over a year ago

This will not show values that do not exist in the table

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related