PostgreSQL: How to Find Intersection of Two Arrays

Often in PostgreSQL you may want to find the intersection of two arrays.

You can use the following syntax to do so:

SELECT *,
       ARRAY(SELECT * FROM UNNEST(day1) WHERE UNNEST = ANY(day2)) AS common
FROM products;

This particular example will create a new column named common that contains the intersection of the arrays from the columns name day1 and day2 of the products table.

The following example shows how to use this syntax in practice.

Example: How to Find Intersection of Two Arrays in PostgreSQL

Suppose that we create the following table named products that shows the names of products sold on two different days at various retail stores:

-- create table named 'products'
CREATE TABLE products (
  store_id INTEGER PRIMARY KEY,
  day1 TEXT[],
  day2 TEXT[]
);

-- add rows to the table 
INSERT INTO products VALUES (0001, '{"A", "B"}', '{"A", "F"}');
INSERT INTO products VALUES (0002, '{"A", "B"}', '{"B", "D"}');
INSERT INTO products VALUES (0003, '{"B", "D"}', '{"A", "C"}');
INSERT INTO products VALUES (0004, '{"A", "C"}', '{"A", "C"}');
INSERT INTO products VALUES (0005, '{"D", "F"}', '{"A", "F"}');

-- view all rows in table
SELECT * FROM products;

 store_id | day1  | day2  
----------+-------+-------
        1 | {A,B} | {A,F}
        2 | {A,B} | {B,D}
        3 | {B,D} | {A,C}
        4 | {A,C} | {A,C}
        5 | {D,F} | {A,F}

Suppose that we would like to find the intersection between the arrays in the day1 and day2 columns.

We can use the following syntax to do so:

SELECT *,
       ARRAY(SELECT * FROM UNNEST(day1) WHERE UNNEST = ANY(day2)) AS common
FROM products;
 
 store_id | day1  | day2  | common 
----------+-------+-------+--------
        1 | {A,B} | {A,F} | {A}
        2 | {A,B} | {B,D} | {B}
        3 | {B,D} | {A,C} | {}
        4 | {A,C} | {A,C} | {A,C}
        5 | {D,F} | {A,F} | {F}

Notice that the new column named common contains the intersection between the arrays in the day1 and day2 columns.

For example, we can see:

• For store 1, item A was the one item that was sold on both days.
• For store 2, item B was the one item that was sold on both days.
• For store 3, there were no common items sold on both days.

And so on.

Feel free to use similar syntax to find the intersection of two arrays in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Random Value from Array
PostgreSQL: Select Rows where Value NOT in Array
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty