How Can I Zip Two Arrays in PostgreSQL?

Zipping Arrays in PostgreSQL

In PostgreSQL, combining two arrays of equal length into pairs can be achieved using various methods.

Versions 9.5 and Later:

Postgres version 9.5 introduced the array_agg function with the ROWS FROM construct, which allows for direct concatenation of arrays:

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

Versions 9.4 and Older:

Prior to 9.5, there are two approaches:

Simple Zipping:

For arrays with the same number of elements, use the following query:

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

Zipping for Multi-Dimensional Arrays:

To convert the zipped arrays into a multi-dimensional array, consider the following custom aggregate function:

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

Use this aggregate function with the unnest operation:

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;

The above is the detailed content of How Can I Zip Two Arrays in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!