Postgres – Function to return the intersection of 2 ARRAYs?

by

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

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  3. How to use RETURNING with ON CONFLICT in PostgreSQL?
  4. PostgreSQL DISTINCT ON with different ORDER BY
  5. PostgreSQL: running count of rows for a query ‘by minute’
  6. Split comma separated column data into additional columns
  7. How to delete duplicate entries?
  8. Escaping keyword-like column names in Postgres
  9. Transpose latest rows per user to columns
  10. Delete duplicate rows (don’t delete all duplicate)
  11. GROUP BY + CASE statement
  12. subquery in FROM must have an alias
  13. Compute percents from SUM() in the same SELECT sql query
  14. SQL Between clause with strings columns
  15. Insert an image in postgresql database
  16. Perform this hours of operation query in PostgreSQL
  17. Getting date list in a range in PostgreSQL
  18. Postgres Dynamic Query Function
  19. How do I delete a fixed number of rows with sorting in PostgreSQL?
  20. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  21. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  22. Postgresql Column Not Found, But Shows in Describe
  23. INSERT COMMAND :: ERROR: column “value” does not exist
  24. Return multiple columns and rows from a function PostgreSQL instead of record
  25. Postgres error updating column data
  26. SQL LIKE condition to check for integer?
  27. Counting the number of occurrences of a substring within a string in PostgreSQL
  28. PostgreSQL: Full Text Search – How to search partial words?
  29. How to bulk insert only new rows in PostreSQL
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment