How can I get unique items from two arrays in PSQL?

To get unique items from two arrays in PSQL (PostgreSQL), you can leverage several approaches. Here’s a comprehensive explanation with examples:

1. Using `UNION` and `unnest`:

- The most straightforward method involves unnesting both arrays and then using `UNION` to combine the results, which naturally eliminates duplicates. `UNION` automatically removes duplicate rows.

- Here's how you do it:

SELECT item FROM unnest(array1) AS item
UNION
SELECT item FROM unnest(array2) AS item;

- Example:

Suppose we have two arrays:

array1 := '{1, 2, 3, 4}';
array2 := '{3, 4, 5, 6}';

The query becomes:

SELECT item FROM unnest('{1,2,3,4}'::int[]) AS item
UNION
SELECT item FROM unnest('{3,4,5,6}'::int[]) AS item;

This would return a result of unique numbers: 1, 2, 3, 4, 5, 6.

2. Using `ARRAY_AGG` and `DISTINCT` after Unnesting:

- Another method involves unnesting, then aggregating back into a new array with `ARRAY_AGG` and using `DISTINCT` to remove the duplicate values before aggregation.

- Here is the query:

SELECT ARRAY_AGG(DISTINCT item)
FROM (
SELECT item FROM unnest(array1) AS item
UNION ALL
SELECT item FROM unnest(array2) AS item
) AS combined_items;

- Example:

Using the same arrays:

SELECT ARRAY_AGG(DISTINCT item)
FROM (
SELECT item FROM unnest('{1,2,3,4}'::int[]) AS item
UNION ALL
SELECT item FROM unnest('{3,4,5,6}'::int[]) AS item
) AS combined_items;

This would result in a single array: {1, 2, 3, 4, 5, 6}.

3. Handling Different Array Types

- If your arrays are not of type integer (e.g. text), you will need to adjust the type casting accordingly. For text arrays use `::text[]`.

4. Choosing the Best Approach

- The method using `UNION` is generally more efficient for large arrays, as it avoids an extra aggregation step. However, if you require the result as a single array, the method with `ARRAY_AGG` is the more appropriate choice.

In summary, using `UNION` with `unnest` is the most efficient way to get unique items from two arrays in PSQL when you need separate rows for each unique item. For a single array output, combine the `unnest` with `ARRAY_AGG` and `DISTINCT` to get a new array with unique values.

Learn more about this