CODEWITHSUNDEEP
sqlarrayspostgresql
fl0cke
fl0cke

Reputation: 2884

PostgreSQL: Exist all in array

I have a postgresql array a = [key1,key2,...] with primary keys and a table foo.

What's the best way to check if all the primary keys in a exist in the table foo?

To clarify: I am looking for a query that returns TRUE if and only if all the keys in the array a exist in the table foo.

Thanks!

Upvotes: 2

Views: 219

Answers (2)

klin
klin

Reputation: 121604

You can count rows from the join of the table and unnested array:

select count(*) = array_length(array[1,2], 1)
from foo
join unnest(array[1,2]) id
using (id);

Upvotes: 1

Grega Bremec
Grega Bremec

Reputation: 304

Being vague in the question, I am going to assume you have the array in some kind of a programming language and you want to check it against the primary key column of the table.

Look at this example of using the contains @> operator (around the middle of the page).

Elaborate in the comments if that is not what you are looking for and please be as specific as you can.

Upvotes: 0

Related Questions