Postgres - Function to return the intersection of 2 ARRAYs?

Question

In postgresql, you can use the && operator to return t (true) if two arrays have common members, i.e. they overlap. Is there a function/operator that will return what those common members are?

i.e. something like this

select arrray_intersection(ARRAY[1, 4, 2], ARRAY[2, 3]);
ARRAY[2]

Also note that the `&&` operator was introduced in version 8.2 some time after the introduction of arrays: https://www.postgresql.org/docs/8.2/functions-array.html — Christophe Roussy, Mar 12 '20 at 15:35

score 89 · Answer 1 · edited May 23 '17 at 12:02

89

Since 8.4, there are useful builtins in Postgres which make the function from the first answer easier and possibly faster (that's what EXPLAIN tells me, anyway: "(cost=0.00..0.07 rows=1 width=64)" for this query vs. "(cost=0.00..60.02 rows=1 width=64)" for the original one).

The simplified code is:

SELECT ARRAY
    (
        SELECT UNNEST(a1)
        INTERSECT
        SELECT UNNEST(a2)
    )
FROM  (
        SELECT  array['two', 'four', 'six'] AS a1
              , array['four', 'six', 'eight'] AS a2
      ) q;

and yeah, you can turn it into a function:

CREATE FUNCTION array_intersect(anyarray, anyarray)
  RETURNS anyarray
  language sql
as $FUNCTION$
    SELECT ARRAY(
        SELECT UNNEST($1)
        INTERSECT
        SELECT UNNEST($2)
    );
$FUNCTION$;

which you can call as

SELECT array_intersect(array['two', 'four', 'six']
                     , array['four', 'six', 'eight']);

But you can just as well call it inline too:

 SELECT array(select unnest(array['two', 'four', 'six']) intersect
              select unnest(array['four', 'six', 'eight']));

edited May 23 '17 at 12:02

Community

1
1

answered Apr 29 '11 at 16:18

bart

7,640
3
33
40

For your second to last example I am getting the error `function array_intersect(text[], text[]) does not exist`. The last example works fine though! – Tom Hemmes Jun 26 '19 at 15:40
how can we make this case insensitive ? – Prim Sep 08 '20 at 11:03
3

To aid optimisation, you could mark the `array_intersect` function as `IMMUTABLE`. – Benjie May 31 '21 at 10:22
Anyone knows TypeORM equivalent for this? – justdvl May 23 '22 at 23:13

score 37 · Answer 2 · answered Apr 16 '09 at 16:17

37

Try & instead of &&

See PostgreSQL Docs for more.

answered Apr 16 '09 at 16:17

dwc

24,196
7
44
55

7

Tested, doesn't work in stock, you need a extension installed to make it work. but +1 anyway . – Kent Fredric Apr 16 '09 at 16:27
1

http://www.postgresql.org/docs/current/static/contrib.html # need to read the data here – Kent Fredric Apr 16 '09 at 16:29

ncank · Answer 3 · 2022-10-31T22:12:27.113

14

just another method..

SELECT ARRAY( SELECT * FROM UNNEST( $1 ) WHERE UNNEST = ANY( $2 ) );

edited Oct 31 '22 at 22:12

answered Oct 10 '12 at 21:20

ncank

946
5
15

2

That worked perfect for me, because it preserves the order. Thank you! – André Sousa Oct 25 '19 at 06:08

score 9 · Answer 4 · edited Jul 28 '17 at 04:57

9

If you don't mind installing an extension, the intarray extension provides the & operator to do this as @dwc pointed out.:

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

Returns {2}.

edited Jul 28 '17 at 04:57

joanolo

6,028
1
29
37

answered May 24 '16 at 16:52

Nick

9,735
7
59
89

3

Which postgresql version are you using? With 9.6 I get the error below and I also can't find it in the docs. Or am I doing something wrong here? ```ERROR: operator does not exist: integer[] & integer[] LINE 1: SELECT ARRAY[1,4,2] & ARRAY[2,3] ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.``` – Dolf Andringa Jul 28 '17 at 03:12
4

Ah, wait, as per https://www.postgresql.org/docs/current/static/intarray.html, it is an extension to postgresql. It would be worth mentioning that. – Dolf Andringa Jul 28 '17 at 03:16
Note that this extension is quite famous and seems to be supported by Heroku, Amazon RDS and probably others. One answer from 2009 by dwc was proposing exactly the same `&` operator but it did not provide a usage example. – Christophe Roussy Mar 12 '20 at 15:40

score 4 · Answer 5 · answered Apr 17 '09 at 09:12

You can use this function:

CREATE OR REPLACE FUNCTION intersection(anyarray, anyarray) RETURNS anyarray as $$
SELECT ARRAY(
    SELECT $1[i]
    FROM generate_series( array_lower($1, 1), array_upper($1, 1) ) i
    WHERE ARRAY[$1[i]] && $2
);
$$ language sql;

It should work with any kind of array, and you can use it like this:

SELECT intersection('{4,2,6}'::INT4[], '{2,3,4}'::INT4[]);

Quassnoi · Answer 6 · 2009-04-16T16:28:59.077

3

SELECT  ARRAY
        (
        SELECT  a1[s]
        FROM    generate_series(array_lower(a1, 1), array_upper(a1, 1)) s
        INTERSECT
        SELECT  a2[s]
        FROM    generate_series(array_lower(a2, 1), array_upper(a2, 1)) s
        )
FROM    (
        SELECT  array['two', 'four', 'six'] AS a1, array['four', 'six', 'eight'] AS a2
        ) q

Works on non-integer arrays too.

edited Apr 16 '09 at 16:28

answered Apr 16 '09 at 16:23

Quassnoi

413,100
91
616
614

score 2 · Answer 7 · answered Aug 03 '22 at 09:51

aother option, for those using for example postgresql, is to take benefits of the jsonb_path_exists tool to check for example if at least one of the elements of an array is also available in another array!

assuming for example here below that we have a table activity_role containing a column "rights" format JSONB, in which we could find for example

table: activity_role

id	rights
1	{"folders":["write","read","assign"]}

if we want to check if for example id 1 has at least one of the following rights ['sign', approve', 'write'], we simply run

SELECT jsonb_path_exists(ar.rights, '$.folders[*] ? (@ == $requested_right)',jsonb_build_object('requested_right', array['sign', 'approve', 'write']) AS authorized FROM activity_rights ar WHERE ar.id = 1;

result will be

authorized

true

because at least 'write' matches in the array1 (rights->'folders') and in the array2 ( array['sign', approve', 'write'] )

score 0 · Answer 8 · answered Aug 08 '22 at 13:59

How the inline operation && would look like in SELECT statement where we are selecting from a 2 tables and filtering only where two array columns intersect (at least 1 value)


SELECT
    table_1 .list::text[],
    table_2 .list::text[]
FROM
    table_1,
    table_2
WHERE
    table_1.list::text[] && table_2.list::text[]

Postgres - Function to return the intersection of 2 ARRAYs?

8 Answers8

Linked