example - postgresql if null then 0

¿Hay algo así como una función zip() en PostgreSQL que combine dos matrices?

Question

Tengo dos valores de matriz de la misma longitud en PostgreSQL:

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

y me gustaría combinarlos en

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

¿Hay una manera de hacer eso?

Answer 1

Aquí hay otro enfoque que es seguro para matrices de diferentes longitudes, usando la matriz de agregación múltiple mencionada por Erwin:

CREATE OR REPLACE FUNCTION zip(array1 anyarray, array2 anyarray) RETURNS text[]
AS $$
SELECT array_agg_mult(ARRAY[ARRAY[array1[i],array2[i]]])
FROM generate_subscripts(
  CASE WHEN array_length(array1,1) >= array_length(array2,1) THEN array1 ELSE array2 END,
  1
) AS subscripts(i)
$$ LANGUAGE sql;

regress=> SELECT zip(''{a,b,c}''::text[],''{d,e,f}''::text[]);
         zip         
---------------------
 {{a,d},{b,e},{c,f}}
(1 row)


regress=> SELECT zip(''{a,b,c}''::text[],''{d,e,f,g}''::text[]);
             zip              
------------------------------
 {{a,d},{b,e},{c,f},{NULL,g}}
(1 row)

regress=> SELECT zip(''{a,b,c,z}''::text[],''{d,e,f}''::text[]);
             zip              
------------------------------
 {{a,d},{b,e},{c,f},{z,NULL}}
(1 row)

Si desea cortar el exceso en lugar de relleno nulo, simplemente cambie la prueba >= longitud a <= lugar.

Esta función no maneja la característica más bien extraña de PostgreSQL que las matrices pueden tener un elemento de declaración que no sea 1, pero en la práctica, nadie realmente utiliza esa característica. Por ejemplo, con una matriz de 3 elementos con índice cero:

regress=> SELECT zip(''{a,b,c}''::text[], array_fill(''z''::text, ARRAY[3], ARRAY[0]));
          zip           
------------------------
 {{a,z},{b,z},{c,NULL}}
(1 row)

mientras que el código de Erwin funciona con tales matrices, e incluso con matrices multidimensionales (al achatarlas), pero no funciona con matrices de diferente longitud.

Las matrices son un poco especiales en PostgreSQL, son un poco flexibles con matrices multidimensionales, índice de origen configurable, etc.

En 9.4, podrás escribir:

SELECT array_agg_mult(ARRAY[ARRAY[a,b])
FROM unnest(array1) WITH ORDINALITY as (o,a)
NATURAL FULL OUTER JOIN
unnest(array2) WITH ORDINALITY as (o,b);

que será mucho mejor, especialmente si se usa una optimización para escanear las funciones juntas en lugar de hacer una clasificación y unirse.

Answer 2

Postgres 9.3 o anterior

Zip simple ()

Considere la siguiente demostración para Postgres 9.3 o anterior :

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

Resultado:

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

Tenga en cuenta que ambas matrices deben tener la misma cantidad de elementos para soltarse en paralelo, o bien se obtiene una combinación cruzada.

Puede envolver esto en una función, si lo desea:

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

Llamada:

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

Mismo resultado.

zip () a matriz multidimensional:

Ahora, si desea agregar ese nuevo conjunto de matrices en una matriz de dos dimensiones , se vuelve más complicado.

SELECT ARRAY (SELECT ...)

o:

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

o:

SELECT array_agg(ARRAY[ARRAY[a,b]]) AS ab
FROM  ...

todos darán como resultado el mismo mensaje de error (probado con la página 9.1.5):

ERROR: no se pudo encontrar el tipo de matriz para el texto del tipo de datos []

Pero hay una forma de evitar esto, ya que trabajamos en esta cuestión estrechamente relacionada .
Crear una función agregada personalizada:

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

Y úsalo así:

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

Resultado:

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

¡Note la capa adicional ARRAY[] ! Sin eso y solo:

SELECT array_agg_mult(ARRAY[a,b]) AS ab
FROM ...

Usted obtiene:

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

Que puede ser útil para otros fines.

Rollo otra función:

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($1) AS a, unnest($2) AS b) x;
$func$;

Llamada:

SELECT zip2(''{a,b,c}''::text[],''{d,e,f}''::text[]); -- or any other array type

Resultado:

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

Postgres 9.4+

Utilice el unnest() ROWS FROM o el unnest() actualizado que toma varias matrices para deshacerse en paralelo. Cada uno puede tener una longitud diferente. Obtienes ( por documentación ):

[...] el número de filas de resultados en este caso es el resultado de la función más grande, con resultados más pequeños rellenos con valores nulos para que coincidan.

Use esta variante más limpia y simple:

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

Postgres 9.5+

envía array_agg(array expression) :

Function                Argument Type(s)   Return Type
array_agg(expression)   any array type     same as argument data type  

Description
input arrays concatenated into array of one higher dimension
(inputs must all have same dimensionality, and cannot be empty or NULL)

Este es un reemplazo array_agg_mult() para mi función agregada personalizada array_agg_mult() implementada en C, que es considerablemente más rápida. Úselo.