40

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?

Christophe Roussy
  • 16,299
  • 4
  • 85
  • 85
Lander
  • 3,369
  • 2
  • 37
  • 53
  • 1
    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: https://www.postgresql.org/docs/8.2/functions-array.html – Christophe Roussy Mar 12 '20 at 15:30

2 Answers2

86

Sure, use the && array-overlaps operator:

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

See array functions and operators.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 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] && list); – Vijay Shegokar May 27 '18 at 18:08
  • @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. – Craig Ringer May 30 '18 at 13:58
  • @CraigRinger - here is my question https://stackoverflow.com/questions/50555297/postgresql-array-comparision-with-list-input-dropwizard-jdbi – Vijay Shegokar May 30 '18 at 15:29
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;
Daniel Sparing
  • 2,163
  • 15
  • 20