How Can I Efficiently Combine Arrays into Multi-Dimensional Arrays in PostgreSQL?

Combining Arrays into Multi-Dimensional Arrays in PostgreSQL

Introduction

PostgreSQL provides various options for combining arrays efficiently, making it possible to manipulate complex data structures with ease.

Simple zip() Function (Same Length Arrays)

To combine two arrays of equal length, you can utilize the unnest() function along with the ARRAY[] constructor:

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() Function Aggregating to Multi-Dimensional Arrays

For aggregating paired elements into a multi-dimensional array, PostgreSQL offers the custom aggregate function array_agg_mult():

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

Usage:

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}}

Alternatively, the zip2() function can be created as a wrapper around array_agg_mult():

CREATE OR REPLACE FUNCTION zip2(anyarray, anyarray)
RETURNS SETOF anyarray LANGUAGE SQL AS
$func$
SELECT array_agg_mult(ARRAY[ARRAY[a,b]])
FROM (SELECT unnest() AS a, unnest() AS b) x;
$func$

Usage:

SELECT zip2('{a,b,c}'::text[], '{d,e,f}'::text[]);

Result:

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

Postgres 9.5 (array_agg(array expression))

In Postgres 9.5 and later, the array_agg(array expression) function can directly combine arrays into a multi-dimensional array, providing a more efficient solution compared to the custom aggregate function. Its usage is similar to the array_agg_mult() function.

The above is the detailed content of How Can I Efficiently Combine Arrays into Multi-Dimensional Arrays in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!