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] 

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

Answer 2

Try & instead of &&

See PostgreSQL Docs for more.