Home >Database >Mysql Tutorial >How Can I Zip Two Arrays in PostgreSQL?

How Can I Zip Two Arrays in PostgreSQL?

Susan Sarandon
Susan SarandonOriginal
2025-01-04 19:02:40923browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn