Tutorial :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]  


Solution:1

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


Solution:2

Try & instead of &&

See PostgreSQL Docs for more.


Solution: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.


Solution:4

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


Solution:5

one another method..

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


Solution:6

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}.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »