PostgreSQL: Exist all in array

Question

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!

klin · Accepted Answer · 2016-01-26 21:52:49Z

1

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);

answered Jan 26, 2016 at 21:52

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

Grega Bremec · Accepted Answer · 2016-01-26 20:40:20Z

0

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.

answered Jan 26, 2016 at 20:40

Grega Bremec

3041 silver badge6 bronze badges

2 Comments

fl0cke Over a year ago

That is unfortunately not what i meant. The array is a posgresql array.

Grega Bremec Over a year ago

I'm not entirely sure this doesn't work with PG arrays. While there is a cast required in the example because the storage type is VARCHAR, try @> with an array type and see.

Collectives™ on Stack Overflow

PostgreSQL: Exist all in array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related