PostgreSQL check if array contains any element from left-hand array

Question

I know that in PostgreSQL you can run a query like:

SELECT (1 = ANY('{1,3,4,7}'::int[])) AS result to check if the right-hand array contains the element 1. I was wondering if there is an easy way to check if the right-hand array contains any element from the left-hand array. Something like:

SELECT ('{2,3}'::int[] = ANY('{1,3,4,7}'::int[])) AS result

Is there an easy way to do this without iterating over the left-hand loop myself?

This is also a keywords issue as the docs uses the term overlap which is correct but not very mathematical, if there is an overlap we can say the arrays (our sets) have an intersection and intersect. This has also been added some time after the introduction of arrays and is thus not proposed as a solution in older questions, since 8.2: postgresql.org/docs/8.2/functions-array.html — Christophe Roussy
– Christophe Roussy, Commented Mar 12, 2020 at 15:30

Craig Ringer · Accepted Answer · 2014-02-13 00:46:18Z

96

Sure, use the && array-overlaps operator:

SELECT ARRAY[1,2] && ARRAY[1,3,4,7];

See array functions and operators.

answered Feb 13, 2014 at 0:46

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

3 Comments

Vijay Shegokar Over a year ago

It is working if run in PostgreSQL command. But can you please tell me the way to run the same query in code. @SqlQuery("SELECT ARRAY[1,2] && <input_array") public abstract Object query(@BindIn("input_array") List<String> list);

Craig Ringer Over a year ago

@VijayShegokar Your question appears to be "how do I bind an array parameter in whatever-my-language-is. Please post a new question after you have searched for existing ones on this topic.

Vijay Shegokar Over a year ago

@CraigRinger - here is my question stackoverflow.com/questions/50555297/…

Daniel Sparing · Accepted Answer · 2014-02-13 00:48:09Z

5

Assuming that your inputs are arrays but it is okay to unwrap them using unnest(), here is a solution:

SELECT count(*)>0
FROM
    (SELECT unnest('{2,3}'::int[]) a1) t1
    join (SELECT unnest('{1,3,4,7}'::int[]) a2) t2
        on t1.a1=t2.a2;

answered Feb 13, 2014 at 0:48

Daniel Sparing

2,18316 silver badges20 bronze badges

2 Comments

Daniel Sparing Over a year ago

oh well && is hands down more concise and hopefully faster.

Lander Over a year ago

Right. Thank you for your answer though because it taught me more about what's possible with Postgres arrays.

Collectives™ on Stack Overflow

PostgreSQL check if array contains any element from left-hand array

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related