Revision history [back]

You can use the UNION ALL operator to merge arrays into a single array while having all their elements included in the final result. Here is an example query:

SELECT array_agg(DISTINCT elems) as merged_array
FROM (
  SELECT unnest(array[1,2,3]) as elems
  UNION ALL
  SELECT unnest(array[4,5,6]) as elems
) subquery

In this example, we have two arrays - [1,2,3] and [4,5,6] - and we want to merge them into a single array while keeping all their elements. We first use the unnest() function to convert each array into a set of rows, with one row per element in the array. We then use the UNION ALL operator to combine the rows from both arrays into a single table. Finally, we use the array_agg() function to aggregate all the elements in the table into a single array, and the DISTINCT keyword to remove any duplicates. The result of this query is a single array [1,2,3,4,5,6].