Postgres - Function to return the intersection of 2 ARRAYs?

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]

From stackoverflow

• Try & instead of &&

  See PostgreSQL Docs for more.

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

  Kent Fredric : http://www.postgresql.org/docs/current/static/contrib.html # need to read the data here

  dwc : Good catch, Kent

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

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