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?

Martelle

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 – Hofmann

Answer 1

Sure, use the && array-overlaps operator:

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

See array functions and operators.

Vinaigrette

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

@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. – Vinaigrette

@CraigRinger - here is my question #50555797 – Ewe

Answer 2

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;

Louanne

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

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