Combining Arrays in PostgreSQL: A Comprehensive Guide

Introduction

PostgreSQL provides various methods for manipulating arrays. One common operation is combining two arrays of equal length into pairs of elements. This article explores different approaches to achieving this task, considering PostgreSQL versions and specific use cases.

Zipping Arrays for Single-Dimensional Output

PostgreSQL 9.5 or later

PostgreSQL 9.5 introduces array_agg(array expression), allowing you to concatenate multiple arrays into one higher-dimensional array. This feature simplifies array combination tasks, eliminating the need for custom aggregate functions.

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

PostgreSQL 9.4

Prior to PostgreSQL 9.5, an alternative approach involves using unnest() with ROWS FROM.

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

Zipping Arrays for Multidimensional Output

For cases where you need to combine arrays into a multidimensional array, a custom aggregate function is necessary.

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

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

Generalized Zip Function

The following function provides a generalized approach to zipping arrays of any type:

CREATE OR REPLACE FUNCTION zip(anyarray, anyarray)
RETURNS SETOF anyarray LANGUAGE SQL AS
$func$
  SELECT array_agg_mult(ARRAY[ARRAY[a, b]])
  FROM unnest($1) AS unnest1(a, b)
  JOIN unnest($2) AS unnest2(a, b)
    ON true;
$func$;

Usage Scenarios

The examples presented in this article showcase different scenarios for combining arrays:

• Simple Zipping: Combining arrays into a set of pairs of elements (single-dimensional output).
• Multidimensional Zipping: Combining arrays into a 2-dimensional array.
• Generalized Zipping: Combining arrays of any type and returning a set of arrays.

Conclusion

PostgreSQL provides multiple options for combining arrays, depending on the version and desired output format. This article has comprehensively covered the available approaches, enabling you to select the most suitable method for your specific needs.