Often in PostgreSQL you may want to find the union of two arrays.
You can use the following syntax to do so:
SELECT store_id, array_agg(distinct v) AS union_values
FROM products, unnest(day1 || day2) AS v
GROUP BY store_id;
This particular example will create a new column named union_values that contains the union 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 Union 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 union of the arrays in each row of the day1 and day2 columns.
We can use the following syntax to do so:
SELECT store_id, day1, day2, array_agg(distinct v) AS union_values
FROM products, unnest(day1 || day2) AS v
GROUP BY store_id;
store_id | day1 | day2 | union_values
----------+-------+-------+--------------
1 | {A,B} | {A,F} | {A,B,F}
2 | {A,B} | {B,D} | {A,B,D}
3 | {B,D} | {A,C} | {A,B,C,D}
4 | {A,C} | {A,C} | {A,C}
5 | {D,F} | {A,F} | {A,D,F}
Notice that the new column named union_values contains the union of values in the arrays in the day1 and day2 columns.
Feel free to use similar syntax to find the union 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
