How to Zip Two Arrays in PostgreSQL to Create a Multi-Dimensional Array?

Postgres: Combining Two Arrays Using a "zip()" Function

Postgres Functions

There are several methods to combine two arrays into a single multidimensional array using Postgres functions:

Postgres 9.5 or later:

• array_agg(array expression): Concatenates all input arrays into an array of one higher dimension.

Postgres 9.4:

• ROWS FROM or unnest(): Unnests multiple arrays in parallel, allowing arrays of different lengths.

Postgres 9.3 or older:

• zip(): A custom function that unnests two arrays simultaneously, creating a new set of arrays.

Simple zip() Function

Consider the following example for Postgres 9.3 or earlier:

SELECT ARRAY[a,b] AS ab
FROM  (
   SELECT unnest('{a,b,c}'::text[]) AS a
        , unnest('{d,e,f}'::text[]) AS b
    ) x;

Result:

  ab
-------
 {a,d}
 {b,e}
 {c,f}

zip() to Multi-Dimensional Array

To aggregate the resulting set of arrays into a 2-dimensional array, a custom aggregate function called array_agg_mult() is needed:

CREATE AGGREGATE array_agg_mult (anyarray) (
   SFUNC    = array_cat
 , STYPE    = anyarray
 , INITCOND = '{}'
);

Then, use it as follows:

SELECT array_agg_mult(ARRAY[ARRAY[a,b]]) AS ab
FROM  (
   SELECT unnest('{a,b,c}'::text[]) AS a
        , unnest('{d,e,f}'::text[]) AS b
    ) x;

Result:

{{a,d},{b,e},{c,f}}

The above is the detailed content of How to Zip Two Arrays in PostgreSQL to Create a Multi-Dimensional Array?. For more information, please follow other related articles on the PHP Chinese website!