Para ilustrar el uso de los tipos de arrays, creamos esta tabla:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

Como se muestra, un tipo de datos de array se nombra añadiendo corchetes ([]) al nombre del tipo de datos de los elementos del array. El comando anterior creará una tabla llamada sal_emp con una columna de tipo text (name), un array unidimensional de tipo integer (pay_by_quarter), que representa el salario del empleado por trimestre, y un array bidimensional de text (schedule), que representa el horario semanal del empleado.

La sintaxis de CREATE TABLE permite especificar el tamaño exacto de los arrays, por ejemplo:

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

Sin embargo, la implementación actual ignora cualquier límite de tamaño de array proporcionado, es decir, el comportamiento es el mismo que para los arrays de longitud no especificada.

La implementación actual tampoco obliga a respetar el número declarado de dimensiones. Los arrays de un tipo de elemento en particular se consideran todos del mismo tipo, independientemente del tamaño o del número de dimensiones. Por lo tanto, declarar el tamaño o el número de dimensiones del array en CREATE TABLE es simplemente documentación; no afecta al comportamiento en tiempo de ejecución.

Se puede utilizar una sintaxis alternativa, que cumple con el estándar SQL mediante el uso de la palabra clave ARRAY, para arrays unidimensionales. pay_by_quarter podría haberse definido como:

    pay_by_quarter  integer ARRAY[4],

O, si no se va a especificar ningún tamaño de array:

    pay_by_quarter  integer ARRAY,

Como antes, sin embargo, PostgreSQL no obliga a cumplir la restricción de tamaño en ningún caso.

Para escribir un valor de array como una constante literal, encierra los valores de los elementos entre llaves y sepáralos por comas. (Si conoces C, esto no es muy diferente de la sintaxis de C para inicializar estructuras). Puedes poner comillas dobles alrededor de cualquier valor de elemento, y debes hacerlo si contiene comas o llaves. (A continuación aparecen más detalles). Por lo tanto, el formato general de una constante de array es el siguiente:

'{ val1 delim val2 delim ... }'

donde delim es el carácter delimitador para el tipo, según lo registrado en su entrada pg_type. Entre los tipos de datos estándar proporcionados en la distribución de PostgreSQL, todos usan una coma (,), excepto el tipo box que usa un punto y coma (;). Cada val es una constante del tipo de elemento del array, o un sub-array. Un ejemplo de una constante de array es:

'{{1,2,3},{4,5,6},{7,8,9}}'

Esta constante es un array bidimensional de 3 por 3 que consta de tres sub-arrays de enteros.

Para establecer un elemento de una constante de array en NULL, escribe NULL como valor del elemento. (Cualquier variante en mayúsculas o minúsculas de NULL servirá). Si deseas un valor de cadena real “NULL”, debes poner comillas dobles a su alrededor.

(Este tipo de constantes de array son en realidad solo un caso especial de las constantes de tipo genérico discutidas en el Section 4.1.2.7. La constante se trata inicialmente como una cadena y se pasa a la rutina de conversión de entrada del array. Podría ser necesaria una especificación de tipo explícita).

Ahora podemos mostrar algunos comandos INSERT:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

El resultado de las dos inserciones anteriores se ve así:

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

Los arrays multidimensionales deben tener extensiones coincidentes para cada dimensión. Un desacuerdo provoca un error, por ejemplo:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  malformed array literal: "{{"meeting", "lunch"}, {"meeting"}}"
DETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.

También se puede utilizar la sintaxis del constructor ARRAY:

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

Ten en cuenta que los elementos del array son constantes o expresiones SQL normales; por ejemplo, las literales de cadena se escriben con comillas simples, en lugar de comillas dobles como se haría en un literal de array. La sintaxis del constructor ARRAY se discute con más detalle en el Section 4.2.12.

Ahora, podemos ejecutar algunas consultas en la tabla. Primero, mostramos cómo acceder a un solo elemento de un array. Esta consulta recupera los nombres de los empleados cuyo pago cambió en el segundo trimestre:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

Los números de subíndice del array se escriben entre corchetes. Por defecto, PostgreSQL utiliza una convención de numeración basada en uno para los arrays, es decir, un array de n elementos comienza con array[1] y termina con array[n].

Esta consulta recupera el pago del tercer trimestre de todos los empleados:

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

También podemos acceder a secciones rectangulares arbitrarias (slices) de un array, o sub-arrays. Una sección de array se denota escribiendo lower-bound:upper-bound para una o más dimensiones del array. Por ejemplo, esta consulta recupera el primer elemento en el horario de Bill para los dos primeros días de la semana:

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

Si alguna dimensión se escribe como una sección, es decir, contiene dos puntos, entonces todas las dimensiones se tratan como secciones. Cualquier dimensión que tenga solo un número único (sin dos puntos) se trata como si fuera desde 1 hasta el número especificado. Por ejemplo, [2] se trata como [1:2], como en este ejemplo:

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

Para evitar confusiones con el caso que no es una sección, es mejor usar la sintaxis de sección para todas las dimensiones, por ejemplo, [1:2][1:1], no [2][1:1].

Es posible omitir el límite inferior (lower-bound) y/o el límite superior (upper-bound) de un especificador de sección; el límite faltante se reemplaza por el límite inferior o superior de los subíndices del array. Por ejemplo:

SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{lunch},{presentation}}
(1 row)

SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

Una expresión de subíndice de array devolverá null si el propio array o cualquiera de las expresiones de subíndice son null. También se devuelve null si un subíndice está fuera de los límites del array (este caso no genera un error). Por ejemplo, si schedule tiene actualmente las dimensiones [1:3][1:2], entonces hacer referencia a schedule[3][3] produce NULL. Del mismo modo, una referencia a un array con el número incorrecto de subíndices produce un null en lugar de un error.

Una expresión de sección de array también produce null si el propio array o cualquiera de las expresiones de subíndice son null. Sin embargo, en otros casos, como al seleccionar una sección de array que está completamente fuera de los límites actuales del array, una expresión de sección produce un array vacío (de cero dimensiones) en lugar de null. (Esto no coincide con el comportamiento de los elementos individuales y se hace por razones históricas). Si la sección solicitada se superpone parcialmente con los límites del array, se reduce silenciosamente solo a la región superpuesta en lugar de devolver null.

Las dimensiones actuales de cualquier valor de array se pueden recuperar con la función array_dims:

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:2]
(1 row)

array_dims produce un resultado de tipo text, lo cual es conveniente para que lo lean las personas pero quizás incómodo para los programas. Las dimensiones también se pueden recuperar con array_upper y array_lower, que devuelven el límite superior e inferior de una dimensión de array especificada, respectivamente:

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)

array_length devolverá la longitud de una dimensión de array especificada:

SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_length
--------------
            2
(1 row)

cardinality devuelve el número total de elementos en un array en todas las dimensiones. Es efectivamente el número de filas que produciría una llamada a unnest:

SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

 cardinality
-------------
           4
(1 row)

Un valor de array se puede reemplazar por completo:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

o utilizando la sintaxis de expresión ARRAY:

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

Un array también se puede actualizar en un solo elemento:

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

o actualizarse en una sección (slice):

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

Las sintaxis de sección con límite inferior y/o límite superior omitidos también se pueden usar, pero solo al actualizar un valor de array que no sea NULL ni de cero dimensiones (de lo contrario, no hay un límite de subíndice existente para sustituir).

Un valor de array almacenado se puede ampliar asignando valores a elementos que aún no están presentes. Cualquier posición entre las que estaban presentes anteriormente y los elementos recién asignados se rellenará con valores nulos. Por ejemplo, si el array myarray tiene actualmente 4 elementos, tendrá seis elementos después de una actualización que asigne un valor a myarray[6]; myarray[5] contendrá null. Actualmente, la ampliación de esta manera solo está permitida para arrays unidimensionales, no para arrays multidimensionales.

La asignación con subíndices permite la creación de arrays que no utilizan subíndices basados en uno. Por ejemplo, se podría asignar a myarray[-2:7] para crear un array con valores de subíndice de -2 a 7.

También se pueden construir nuevos valores de array utilizando el operador de concatenación, ||:

SELECT ARRAY[1,2] || ARRAY[3,4];
 ?column?
-----------
 {1,2,3,4}
(1 row)

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
      ?column?
---------------------
 {{5,6},{1,2},{3,4}}
(1 row)

El operador de concatenación permite agregar un solo elemento al principio o al final de un array unidimensional. También acepta dos arrays de dimensión N, o un array de dimensión N y uno de dimensión N+1.

Cuando se agrega un solo elemento al principio o al final de un array unidimensional, el resultado es un array con el mismo subíndice de límite inferior que el operando del array. Por ejemplo:

SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
 array_dims
------------
 [0:2]
(1 row)

SELECT array_dims(ARRAY[1,2] || 3);
 array_dims
------------
 [1:3]
(1 row)

Cuando se concatenan dos arrays con un número igual de dimensiones, el resultado conserva el subíndice de límite inferior de la dimensión externa del operando de la izquierda. El resultado es un array que comprende cada elemento del operando de la izquierda seguido de cada elemento del operando de la derecha. Por ejemplo:

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
 array_dims
------------
 [1:5]
(1 row)

SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
 array_dims
------------
 [1:5][1:2]
(1 row)

Cuando un array de dimensión N se agrega al principio o al final de un array de dimensión N+1, el resultado es análogo al caso del elemento-array anterior. Cada sub-array de dimensión N es esencialmente un elemento de la dimensión externa del array de dimensión N+1. Por ejemplo:

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 array_dims
------------
 [1:3][1:2]
(1 row)

Un array también se puede construir utilizando las funciones array_prepend, array_append, o array_cat. Las dos primeras solo admiten arrays unidimensionales, pero array_cat admite arrays multidimensionales. Algunos ejemplos:

SELECT array_prepend(1, ARRAY[2,3]);
 array_prepend
---------------
 {1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);
 array_append
--------------
 {1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
 array_cat
 -----------
 {1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
      array_cat
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
      array_cat
---------------------
 {{5,6},{1,2},{3,4}}

En casos simples, se prefiere el operador de concatenación discutido anteriormente en lugar del uso directo de estas funciones. Sin embargo, debido a que el operador de concatenación está sobrecargado para servir en los tres casos, hay situaciones en las que el uso de una de las funciones es útil para evitar la ambigüedad. Por ejemplo, considera:

SELECT ARRAY[1, 2] || '{3, 4}';  -- la literal sin tipo se toma como un array
 ?column?
-----------
 {1,2,3,4}

SELECT ARRAY[1, 2] || '7';                 -- también esta
ERROR:  malformed array literal: "7"

SELECT ARRAY[1, 2] || NULL;                -- también un NULL sin decorar
 ?column?
----------
 {1,2}
(1 row)

SELECT array_append(ARRAY[1, 2], NULL);    -- esto podría haber sido lo planeado
 array_append
--------------
 {1,2,NULL}

En los ejemplos anteriores, el analizador ve un array de enteros en un lado del operador de concatenación, y una constante de tipo indeterminado en el otro. La heurística que utiliza para resolver el tipo de la constante es asumir que es del mismo tipo que la otra entrada del operador — en este caso, un array de enteros. Por lo tanto, se presume que el operador de concatenación representa a array_cat, no a array_append. Cuando esa es la elección incorrecta, podría solucionarse convirtiendo (casting) la constante al tipo de elemento del array; pero el uso explícito de array_append podría ser una solución preferible.

Para buscar un valor en un array, se debe comprobar cada valor. Esto se puede hacer manualmente si conoces el tamaño del array. Por ejemplo:

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

Sin embargo, esto se vuelve tedioso rápidamente para arrays grandes, y no es útil si se desconoce el tamaño del array. En el Section 9.25 se describe un método alternativo. La consulta anterior podría reemplazarse por:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

Además, puedes encontrar filas donde el array tenga todos los valores iguales a 10000 con:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Alternativamente, se puede utilizar la función generate_subscripts. Por ejemplo:

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

Esta función se describe en el Table 9.70.

También puedes buscar en un array utilizando el operador &&, que comprueba si el operando izquierdo se superpone con el operando derecho. Por ejemplo:

SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

Este y otros operadores de arrays se describen detalladamente en el Section 9.19. Se puede acelerar mediante un índice apropiado, como se describe en el Section 11.2.

También puedes buscar valores específicos en un array utilizando las funciones array_position y array_positions. La primera devuelve el subíndice de la primera ocurrencia de un valor en un array; la segunda devuelve un array con los subíndices de todas las ocurrencias del valor en el array. Por ejemplo:

SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
 array_position
----------------
              2
(1 row)

SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
 array_positions
-----------------
 {1,4,8}
(1 row)

La representación de texto externa de un valor de array consta de elementos que se interpretan de acuerdo con las reglas de conversión de entrada/salida para el tipo de elemento del array, más la decoración que indica la estructura del array. La decoración consta de llaves ({ y }) alrededor del valor del array más caracteres delimitadores entre elementos adyacentes. El carácter delimitador suele ser una coma (,) pero puede ser otro: se determina mediante la configuración de typdelim para el tipo de elemento del array. Entre los tipos de datos estándar proporcionados en la distribución de PostgreSQL, todos usan una coma, excepto el tipo box, que usa un punto y coma (;). En un array multidimensional, cada dimensión (fila, plano, cubo, etc.) obtiene su propio nivel de llaves, y se deben escribir delimitadores entre entidades adyacentes rodeadas por llaves del mismo nivel.

La rutina de salida del array pondrá comillas dobles alrededor de los valores de los elementos si son cadenas vacías, contienen llaves, caracteres delimitadores, comillas dobles, barras diagonales inversas (backslashes) o espacios en blanco, o si coinciden con la palabra NULL. Las comillas dobles y las barras diagonales inversas incrustadas en los valores de los elementos se escaparán con una barra diagonal inversa. Para los tipos de datos numéricos es seguro asumir que nunca aparecerán comillas dobles, pero para los tipos de datos de texto uno debe estar preparado para lidiar tanto con la presencia como con la ausencia de comillas.

Por defecto, el valor del índice del límite inferior de las dimensiones de un array se establece en uno. Para representar arrays con otros límites inferiores, los rangos de subíndices del array se pueden especificar explícitamente antes de escribir el contenido del array. Esta decoración consta de corchetes ([]) alrededor de los límites inferior y superior de cada dimensión del array, con un carácter delimitador de dos puntos (:) en el medio. La decoración de la dimensión del array va seguida de un signo igual (=). Por ejemplo:

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

 e1 | e2
----+----
  1 |  6
(1 row)

La rutina de salida del array incluirá dimensiones explícitas en su resultado solo cuando haya uno o más límites inferiores diferentes de uno.

Si el valor escrito para un elemento es NULL (en cualquier variante de mayúsculas o minúsculas), se considera que el elemento es NULL. La presencia de comillas o barras diagonales inversas deshabilita esto y permite ingresar el valor de cadena literal “NULL”. Además, para compatibilidad con versiones anteriores de PostgreSQL anteriores a la 8.2, el parámetro de configuración array_nulls se puede establecer en off para suprimir el reconocimiento de NULL como un NULL.

Como se mostró anteriormente, al escribir un valor de array puedes usar comillas dobles alrededor de cualquier elemento individual del array. Debes hacerlo si el valor del elemento pudiera confundir de otro modo al analizador de valores de array. Por ejemplo, los elementos que contienen llaves, comas (o el carácter delimitador del tipo de datos), comillas dobles, barras diagonales internas o espacios en blanco al principio o al final deben estar entre comillas dobles. Las cadenas vacías y las cadenas que coinciden con la palabra NULL también deben estar entre comillas. Para poner una comilla doble o una barra diagonal inversa en el valor de un elemento de array entre comillas, colócala precedida de una barra diagonal inversa. Alternativamente, puedes evitar las comillas y usar el escape con barra diagonal inversa para proteger todos los caracteres de datos que de otro modo se tomarían como sintaxis de array.

Puedes añadir espacios en blanco antes de una llave izquierda o después de una llave derecha. También puedes añadir espacios en blanco antes o después de cualquier cadena de elemento individual. En todos estos casos se ignorará el espacio en blanco. Sin embargo, el espacio en blanco dentro de los elementos entre comillas dobles, o rodeado a ambos lados por caracteres que no sean espacios en blanco de un elemento, no se ignora.