Se puede hacer referencia a una columna en la forma:

correlación.nombre_columna

correlación es el nombre de una tabla (posiblemente calificada con un nombre de esquema), o un alias para una tabla definida mediante una cláusula FROM. El nombre de correlación y el punto de separación se pueden omitir si el nombre de la columna es único en todas las tablas que se están utilizando en la consulta actual. (Véase también la Chapter 7).

Una referencia a un parámetro posicional se utiliza para indicar un valor que se suministra externamente a una sentencia SQL. Los parámetros se utilizan en las definiciones de funciones SQL y en las consultas preparadas. Algunas bibliotecas de clientes también admiten la especificación de valores de datos por separado de la cadena de comandos SQL, en cuyo caso se utilizan parámetros para hacer referencia a los valores de datos fuera de línea. La forma de una referencia de parámetro es:

$número

Por ejemplo, considera la definición de una función, dept, como:

CREATE FUNCTION dept(text) RETURNS dept
    AS $$ SELECT * FROM dept WHERE name = $1 $$
    LANGUAGE SQL;

Aquí, $1 hace referencia al valor del primer argumento de la función cada vez que se invoca la función.

Si una expresión produce un valor de tipo matriz (array), entonces se puede extraer un elemento específico del valor de la matriz escribiendo:

expresión[subíndice]

o se pueden extraer múltiples elementos adyacentes (una “rebanada de matriz”) escribiendo:

expresión[subíndice_inferior:subíndice_superior]

(Aquí, los corchetes [ ] deben aparecer literalmente). Cada subíndice es en sí mismo una expresión, que se redondeará al valor entero más cercano.

En general, la expresión de matriz debe estar entre paréntesis, pero los paréntesis se pueden omitir cuando la expresión a la que se le va a aplicar el subíndice es solo una referencia a una columna o un parámetro posicional. Además, se pueden concatenar múltiples subíndices cuando la matriz original es multidimensional. Por ejemplo:

mytable.arraycolumn[4]
mytable.two_d_column[17][34]
$1[10:42]
(arrayfunction(a,b))[42]

Los paréntesis en el último ejemplo son obligatorios. Consulta la Section 8.15 para más información sobre matrices.

Si una expresión produce un valor de tipo compuesto (tipo fila), entonces se puede extraer un campo específico de la fila escribiendo:

expresión.nombre_campo

En general, la expresión de fila debe estar entre paréntesis, pero los paréntesis se pueden omitir cuando la expresión de la que se va a seleccionar es solo una referencia a una tabla o un parámetro posicional. Por ejemplo:

mytable.mycolumn
$1.somecolumn
(rowfunction(a,b)).col3

(Por lo tanto, una referencia de columna calificada es en realidad solo un caso especial de la sintaxis de selección de campo). Un caso especial importante es extraer un campo de una columna de tabla que es de un tipo compuesto:

(compositecol).somefield
(mytable.compositecol).somefield

Los paréntesis son necesarios aquí para mostrar que compositecol es un nombre de columna y no un nombre de tabla, o que mytable es un nombre de tabla y no un nombre de esquema en el segundo caso.

Puedes solicitar todos los campos de un valor compuesto escribiendo .*:

(compositecol).*

Este comportamiento varía según el contexto; consulta la Section 8.16.5 para más detalles.

Hay dos sintaxis posibles para una invocación de operador:

donde el token operador sigue las reglas sintácticas de la Section 4.1.3, o es una de las palabras clave AND, OR y NOT, o es un nombre de operador calificado en la forma:

OPERATOR(esquema.nombre_operador)

Qué operadores particulares existen y si son unarios o binarios depende de los operadores que hayan sido definidos por el sistema o por el usuario. La Chapter 9 describe los operadores integrados.

La sintaxis para una llamada a función es el nombre de una función (posiblemente calificado con un nombre de esquema), seguido de su lista de argumentos entre paréntesis:

nombre_función ([expresión [, expresión ... ]] )

Por ejemplo, lo siguiente calcula la raíz cuadrada de 2:

sqrt(2)

La lista de funciones integradas está en la Chapter 9. El usuario puede añadir otras funciones.

Al emitir consultas en una base de datos donde algunos usuarios desconfían de otros, observa las precauciones de seguridad de la Section 10.3 al escribir llamadas a funciones.

Opcionalmente, los argumentos pueden tener nombres asociados. Consulta la Section 4.3 para más detalles.

Una expresión de agregación representa la aplicación de una función de agregación a las filas seleccionadas por una consulta. Una función de agregación reduce múltiples entradas a un único valor de salida, como la suma o el promedio de las entradas. La sintaxis de una expresión de agregación es una de las siguientes:

nombre_agregado (expresión [ , ... ] [ cláusula_order_by ] ) [ FILTER ( WHERE cláusula_filtro ) ]
nombre_agregado (ALL expresión [ , ... ] [ cláusula_order_by ] ) [ FILTER ( WHERE cláusula_filtro ) ]
nombre_agregado (DISTINCT expresión [ , ... ] [ cláusula_order_by ] ) [ FILTER ( WHERE cláusula_filtro ) ]
nombre_agregado ( * ) [ FILTER ( WHERE cláusula_filtro ) ]
nombre_agregado ( [ expresión [ , ... ] ] ) WITHIN GROUP ( cláusula_order_by ) [ FILTER ( WHERE cláusula_filtro ) ]

donde nombre_agregado es un agregado previamente definido (posiblemente calificado con un nombre de esquema) y expresión es cualquier expresión de valor que no contenga ella misma una expresión de agregación o una llamada a una función de ventana. Las opcionales cláusula_order_by y cláusula_filtro se describen a continuación.

La primera forma de expresión de agregación invoca el agregado una vez por cada fila de entrada. La segunda forma es idéntica a la primera, ya que ALL es la opción por omisión. La tercera forma invoca el agregado una vez por cada valor distinto de la expresión (o conjunto distinto de valores, para múltiples expresiones) que se encuentre en las filas de entrada. La cuarta forma invoca el agregado una vez por cada fila de entrada; dado que no se especifica ningún valor de entrada en particular, generalmente solo es útil para la función de agregación count(*). La última forma se utiliza con funciones de agregación de conjunto ordenado (ordered-set), que se describen a continuación.

La mayoría de las funciones de agregación ignoran las entradas nulas, de modo que las filas en las que una o más de las expresiones resultan en nulo son descartadas. Se puede asumir que esto es así, a menos que se especifique lo contrario, para todos los agregados integrados.

Por ejemplo, count(*) produce el número total de filas de entrada; count(f1) produce el número de filas de entrada en las que f1 no es nulo, ya que count ignora los nulos; y count(distinct f1) produce el número de valores distintos no nulos de f1.

Normalmente, las filas de entrada se entregan a la función de agregación en un orden no especificado. En muchos casos esto no importa; por ejemplo, min produce el mismo resultado sin importar en qué orden reciba las entradas. Sin embargo, algunas funciones de agregación (como array_agg y string_agg) producen resultados que dependen del orden de las filas de entrada. Al utilizar dichos agregados, se puede usar la opcional cláusula_order_by para especificar el orden deseado. La cláusula_order_by tiene la misma sintaxis que la cláusula ORDER BY a nivel de consulta, como se describe en la Section 7.5, excepto que sus expresiones son siempre simples expresiones y no pueden ser nombres o números de columnas de salida. Por ejemplo:

WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
SELECT array_agg(v ORDER BY v DESC) FROM vals;
  array_agg
-------------
 {4,3,3,2,1}

Dado que jsonb solo conserva la última clave coincidente, el orden de sus claves puede ser significativo:

WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') )
SELECT jsonb_object_agg(k, v ORDER BY v) FROM vals;
      jsonb_object_agg
----------------------------
 {"key0": "1", "key1": "3"}

Cuando trabajes con funciones de agregación de múltiples argumentos, ten en cuenta que la cláusula ORDER BY va después de todos los argumentos del agregado. Por ejemplo, escribe esto:

SELECT string_agg(a, ',' ORDER BY a) FROM table;

no esto:

SELECT string_agg(a ORDER BY a, ',') FROM table;  -- incorrecto

Este último es sintácticamente válido, pero representa una llamada a una función de agregación de un solo argumento con dos claves de ORDER BY (siendo la segunda bastante inútil ya que es una constante).

Si se especifica DISTINCT junto con una cláusula_order_by, las expresiones de ORDER BY solo pueden hacer referencia a columnas que estén en la lista de DISTINCT. Por ejemplo:

WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;
  array_agg
-----------
 {4,3,2,1}

Colocar ORDER BY dentro de la lista de argumentos regulares del agregado, como se ha descrito hasta ahora, se utiliza cuando se ordenan las filas de entrada para agregados estadísticos y de propósito general, para los cuales el ordenamiento es opcional. Existe una subclase de funciones de agregación llamadas agregados de conjunto ordenado (ordered-set aggregates) para los cuales una cláusula_order_by es requerida, generalmente porque el cálculo del agregado solo tiene sentido en términos de un ordenamiento específico de sus filas de entrada. Los ejemplos típicos de agregados de conjunto ordenado incluyen los cálculos de rango (rank) y percentil. Para un agregado de conjunto ordenado, la cláusula_order_by se escribe dentro de WITHIN GROUP (...), como se muestra en la última alternativa de sintaxis anterior. Las expresiones en la cláusula_order_by se evalúan una vez por fila de entrada al igual que los argumentos regulares del agregado, se ordenan según los requisitos de la cláusula_order_by, y se pasan a la función de agregación como argumentos de entrada. (Esto difiere del caso de una cláusula_order_by que no esté bajo WITHIN GROUP, la cual no se trata como argumento(s) para la función de agregación). Las expresiones de argumento que preceden a WITHIN GROUP, si las hay, se denominan argumentos directos para distinguirlos de los argumentos agregados enumerados en la cláusula_order_by. A diferencia de los argumentos de agregación normales, los argumentos directos se evalúan solo una vez por llamada al agregado, no una vez por fila de entrada. Esto significa que pueden contener variables solo si esas variables están agrupadas por GROUP BY; esta restricción es la misma que si los argumentos directos no estuvieran dentro de una expresión de agregación en absoluto. Los argumentos directos se utilizan típicamente para cosas como fracciones de percentil, que solo tienen sentido como un único valor por cálculo de agregación. La lista de argumentos directos puede estar vacía; en este caso, escribe simplemente () y no (*). (PostgreSQL en realidad aceptará cualquiera de las dos formas, pero solo la primera cumple con el estándar SQL).

Un ejemplo de una llamada a un agregado de conjunto ordenado es:

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;
 percentile_cont
-----------------
           50489

que obtiene el percentil 50, o mediana, del valor de la columna income de la tabla households. Aquí, 0.5 es un argumento directo; no tendría sentido que la fracción del percentil fuera un valor que variara entre filas.

Si se especifica FILTER, entonces solo las filas de entrada para las cuales la cláusula_filtro se evalúa como verdadera se pasan a la función de agregación; las demás filas son descartadas. Por ejemplo:

SELECT
    count(*) AS unfiltered,
    count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
 unfiltered | filtered
------------+----------
         10 |        4
(1 row)

Las funciones de agregación predefinidas se describen en la Section 9.21. El usuario puede añadir otras funciones de agregación.

Una expresión de agregación solo puede aparecer en la lista de resultados o en la cláusula HAVING de un comando SELECT. Está prohibida en otras cláusulas, como WHERE, porque esas cláusulas se evalúan lógicamente antes de que se formen los resultados de los agregados.

Cuando una expresión de agregación aparece en una subconsulta (consulta la Section 4.2.11 y la Section 9.24), el agregado normalmente se evalúa sobre las filas de la subconsulta. Pero ocurre una excepción si los argumentos del agregado (y la cláusula_filtro, si la hay) contienen únicamente variables del nivel externo: el agregado entonces pertenece al nivel externo más cercano y se evalúa sobre las filas de esa consulta. La expresión de agregación en su conjunto es entonces una referencia externa para la subconsulta en la que aparece, y actúa como una constante sobre cualquier evaluación individual de esa subconsulta. La restricción de aparecer únicamente en la lista de resultados o en la cláusula HAVING se aplica con respecto al nivel de consulta al que pertenece el agregado.

Una llamada a función de ventana representa la aplicación de una función similar a un agregado sobre una porción de las filas seleccionadas por una consulta. A diferencia de las llamadas a agregados normales, esto no está ligado a agrupar las filas seleccionadas en una única fila de salida; cada fila sigue estando separada en la salida de la consulta. Sin embargo, la función de ventana tiene acceso a todas las filas que formarían parte del grupo de la fila actual según la especificación de agrupación (lista PARTITION BY) de la llamada a la función de ventana. La sintaxis de una llamada a función de ventana es una de las siguientes:

nombre_función ([expresión [, expresión ... ]]) [ FILTER ( WHERE cláusula_filtro ) ] OVER nombre_ventana
nombre_función ([expresión [, expresión ... ]]) [ FILTER ( WHERE cláusula_filtro ) ] OVER ( definición_ventana )
nombre_función ( * ) [ FILTER ( WHERE cláusula_filtro ) ] OVER nombre_ventana
nombre_función ( * ) [ FILTER ( WHERE cláusula_filtro ) ] OVER ( definición_ventana )

donde definición_ventana tiene la sintaxis

[ nombre_ventana_existente ]
[ PARTITION BY expresión [, ...] ]
[ ORDER BY expresión [ ASC | DESC | USING operador ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ cláusula_marco ]

La opcional cláusula_marco puede ser una de las siguientes:

{ RANGE | ROWS | GROUPS } inicio_marco [ exclusión_marco ]
{ RANGE | ROWS | GROUPS } BETWEEN inicio_marco AND fin_marco [ exclusión_marco ]

donde inicio_marco y fin_marco pueden ser una de las siguientes:

UNBOUNDED PRECEDING
desplazamiento PRECEDING
CURRENT ROW
desplazamiento FOLLOWING
UNBOUNDED FOLLOWING

and exclusión_marco can be one of

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

Aquí, expresión representa cualquier expresión de valor que no contenga llamadas a funciones de ventana en sí misma.

nombre_ventana es una referencia a una especificación de ventana con nombre definida en la cláusula WINDOW de la consulta. Alternativamente, se puede proporcionar una definición_ventana completa entre paréntesis, utilizando la misma sintaxis que para definir una ventana con nombre en la cláusula WINDOW; consulta la página de referencia de SELECT para más detalles. Vale la pena señalar que OVER wname no es exactamente equivalente a OVER (wname ...); este último implica copiar y modificar la definición de la ventana, y será rechazado si la especificación de la ventana de referencia incluye una cláusula de marco.

La cláusula PARTITION BY agrupa las filas de la consulta en particiones, las cuales son procesadas por separado por la función de ventana. PARTITION BY funciona de manera similar a una cláusula GROUP BY a nivel de consulta, excepto que sus expresiones son siempre simples expresiones y no pueden ser nombres o números de columnas de salida. Sin PARTITION BY, todas las filas producidas por la consulta son tratadas como una única partición. La cláusula ORDER BY determina el orden en el que las filas de una partición son procesadas por la función de ventana. Funciona de manera similar a una cláusula ORDER BY a nivel de consulta, pero de igual modo no puede usar nombres o números de columnas de salida. Sin ORDER BY, las filas son procesadas en un orden no especificado.

La cláusula_marco especifica el conjunto de filas que constituyen el marco de ventana, el cual es un subconjunto de la partición actual, para aquellas funciones de ventana que actúan sobre el marco en lugar de sobre toda la partición. El conjunto de filas en el marco puede variar según cuál fila sea la fila actual. El marco se puede especificar en el modo RANGE, ROWS o GROUPS; en cada caso, va desde el inicio_marco hasta el fin_marco. Si se omite fin_marco, el final por omisión es CURRENT ROW.

Un inicio_marco con UNBOUNDED PRECEDING significa que el marco comienza con la primera fila de la partición, y de manera similar un fin_marco con UNBOUNDED FOLLOWING significa que el marco termina con la última fila de la partición.

En el modo RANGE o GROUPS, un inicio_marco de CURRENT ROW significa que el marco comienza con la primera fila par de la fila actual (una fila que la cláusula ORDER BY de la ventana clasifica como equivalente a la fila actual), mientras que un fin_marco de CURRENT ROW significa que el marco termina con la última fila par de la fila actual. En el modo ROWS, CURRENT ROW simplemente significa la fila actual.

En las opciones de marco desplazamiento PRECEDING and desplazamiento FOLLOWING, el desplazamiento debe ser una expresión que no contenga variables, funciones de agregación o funciones de ventana. El significado del desplazamiento depende del modo de marco:

En cualquier caso, la distancia al final del marco está limitada por la distancia al final de la partición, de modo que para filas cercanas a los extremos de la partición, el marco podría contener menos filas que en otros lugares.

Ten en cuenta que tanto en el modo ROWS como en el modo GROUPS, 0 PRECEDING y 0 FOLLOWING son equivalentes a CURRENT ROW. Esto normalmente también se aplica en el modo RANGE, para un significado específico del tipo de datos correspondiente a “cero”.

La opción exclusión_marco permite excluir del marco las filas alrededor de la fila actual, incluso si se incluyeran de acuerdo con las opciones de inicio y fin del marco. EXCLUDE CURRENT ROW excluye la fila actual del marco. EXCLUDE GROUP excluye del marco la fila actual y sus pares de ordenamiento. EXCLUDE TIES excluye cualquier par de la fila actual del marco, pero no la fila actual en sí. EXCLUDE NO OTHERS simplemente especifica explícitamente el comportamiento por omisión de no excluir la fila actual ni sus pares.

La opción de marco por omisión es RANGE UNBOUNDED PRECEDING, que es lo mismo que RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Con ORDER BY, esto establece que el marco sea todas las filas desde el inicio de la partición hasta el último par de la fila actual según ORDER BY. Sin ORDER BY, esto significa que todas las filas de la partición están incluidas en el marco de la ventana, ya que todas las filas se convierten en pares de la fila actual.

Las restricciones son que inicio_marco no puede ser UNBOUNDED FOLLOWING, fin_marco no puede ser UNBOUNDED PRECEDING, y la elección de fin_marco no puede aparecer antes en la lista anterior de opciones de inicio_marco y fin_marco que la elección de inicio_marco (por ejemplo, no se permite RANGE BETWEEN CURRENT ROW AND desplazamiento PRECEDING). Pero, por ejemplo, se permite ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING, aunque nunca seleccionaría ninguna fila.

Si se especifica FILTER, entonces solo las filas de entrada para las cuales la cláusula_filtro se evalúa como verdadera se pasan a la función de ventana; las demás filas son descartadas. Solo las funciones de ventana que son agregados aceptan una cláusula FILTER.

Las funciones de ventana integradas se describen en la Table 9.67. El usuario puede añadir otras funciones de ventana. También, cualquier agregado estadístico o de propósito general integrado o definido por el usuario se puede usar como función de ventana. (Los agregados de conjunto ordenado e hipotético no se pueden usar actualmente como funciones de ventana).

Las sintaxis que utilizan * se utilizan para llamar a funciones de agregación sin parámetros como funciones de ventana, por ejemplo count(*) OVER (PARTITION BY x ORDER BY y). El asterisco (*) no se suele utilizar para funciones específicas de ventana. Las funciones específicas de ventana no permiten el uso de DISTINCT o ORDER BY dentro de la lista de argumentos de la función.

Las llamadas a funciones de ventana se permiten únicamente en la lista de SELECT y en la cláusula ORDER BY de la consulta.

Se puede encontrar más información sobre las funciones de ventana en la Section 3.5, la Section 9.22 y la Section 7.2.5.

Una conversión de tipo (type cast) especifica la conversión de un tipo de datos a otro. PostgreSQL acepta dos sintaxis equivalentes para las conversiones de tipo:

CAST ( expresión AS tipo )
expresión::tipo

La sintaxis de CAST cumple con el estándar SQL; la sintaxis con :: es el uso histórico de PostgreSQL.

Cuando se aplica una conversión a una expresión de valor de un tipo conocido, esta representa una conversión de tipo en tiempo de ejecución. La conversión tendrá éxito solo si se ha definido una operación de conversión de tipo adecuada. Ten en cuenta que esto es sutilmente diferente del uso de conversiones con constantes, como se muestra en la Section 4.1.2.7. Una conversión aplicada a un literal de cadena simple representa la asignación inicial de un tipo a un valor de constante literal, por lo que tendrá éxito para cualquier tipo (siempre que el contenido del literal de cadena sea una sintaxis de entrada aceptable para el tipo de datos).

Por lo general, se puede omitir una conversión de tipo explícita si no hay ambigüedad en cuanto al tipo que debe producir una expresión de valor (por ejemplo, cuando se asigna a una columna de tabla); el sistema aplicará automáticamente una conversión de tipo en tales casos. Sin embargo, la conversión automática solo se realiza para las conversiones que están marcadas como “adecuadas para aplicar implícitamente” en los catálogos del sistema. Otras conversiones deben invocarse con la sintaxis de conversión explícita. Esta restricción tiene como objetivo evitar que se apliquen silenciosamente conversiones sorprendentes.

También es posible especificar una conversión de tipo utilizando una sintaxis similar a una llamada a función:

nombre_tipo ( expresión )

Sin embargo, esto solo funciona para tipos cuyos nombres también sean válidos como nombres de función. Por ejemplo, double precision no se puede usar de esta manera, pero el equivalente float8 sí. Además, los nombres interval, time y timestamp solo se pueden usar de esta forma si se escriben entre comillas dobles, debido a conflictos sintácticos. Por lo tanto, el uso de la sintaxis de conversión similar a una función provoca inconsistencias y probablemente debería evitarse.

La cláusula COLLATE anula la colación (ordenamiento) de una expresión. Se añade al final de la expresión a la que se aplica:

expr COLLATE colación

donde colación es un identificador que posiblemente esté calificado con el esquema. La cláusula COLLATE se vincula con mayor fuerza que los operadores; se pueden usar paréntesis cuando sea necesario.

Si no se especifica explícitamente ninguna colación, el sistema de base de datos obtiene una colación a partir de las columnas involucradas en la expresión, o bien toma la colación por omisión de la base de datos si no hay ninguna columna involucrada en la expresión.

Los dos usos comunes de la cláusula COLLATE son anular el orden de clasificación en una cláusula ORDER BY, por ejemplo:

SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";

and overriding the collation of a function or operator call that has locale-sensitive results, for example:

SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";

Ten en cuenta que en este último caso la cláusula COLLATE se asocia a un argumento de entrada del operador que queremos afectar. No importa a qué argumento del operador o de la llamada a la función se asocie la cláusula COLLATE, porque la colación que aplica el operador o la función se obtiene considerando todos los argumentos, y una cláusula COLLATE explícita anulará las colaciones de todos los demás argumentos. (Sin embargo, asociar cláusulas COLLATE incompatibles a más de un argumento es un error. Para más detalles, consulta la Section 23.2). Por lo tanto, esto da el mismo resultado que el ejemplo anterior:

SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';

Pero esto es un error:

SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";

porque intenta aplicar una colación al resultado del operador >, que es del tipo de datos no ordenable boolean.

Una subconsulta escalar es una consulta SELECT ordinaria entre paréntesis que devuelve exactamente una fila con una columna. (Consulta la Chapter 7 para obtener información sobre cómo escribir consultas). La consulta SELECT se ejecuta y el único valor devuelto se utiliza en la expresión de valor circundante. Es un error utilizar una consulta que devuelva más de una fila o más de una columna como subconsulta escalar. (Pero si, durante una ejecución en particular, la subconsulta no devuelve filas, no hay error; el resultado escalar se considera nulo). La subconsulta puede hacer referencia a variables de la consulta circundante, las cuales actuarán como constantes durante cualquier evaluación de la subconsulta. Consulta también la Section 9.24 para otras expresiones que involucran subconsultas.

Por ejemplo, lo siguiente busca la población de la ciudad más grande en cada estado:

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
    FROM states;

Un constructor de array es una expresión que construye un valor de array utilizando los valores para sus elementos miembros. Un constructor de array simple consiste en la palabra clave ARRAY, un corchete izquierdo [, una lista de expresiones (separadas por comas) para los valores de los elementos del array y, finalmente, un corchete derecho ]. Por ejemplo:

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

Por omisión, el tipo de elemento del array es el tipo común de las expresiones miembro, determinado utilizando las mismas reglas que para las construcciones UNION o CASE (consulta la Section 10.5). Puedes anular esto convirtiendo explícitamente el constructor del array al tipo deseado, por ejemplo:

SELECT ARRAY[1,2,22.7]::integer[];
  array
----------
 {1,2,23}
(1 row)

Esto tiene el mismo efecto que convertir cada expresión al tipo de elemento del array de forma individual. Para más información sobre la conversión, consulta la Section 4.2.9.

Los valores de arrays multidimensionales se pueden construir anidando constructores de arrays. En los constructores internos, se puede omitir la palabra clave ARRAY. Por ejemplo, estos producen el mismo resultado:

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

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

Dado que los arrays multidimensionales deben ser rectangulares, los constructores internos en el mismo nivel deben producir sub-arrays de dimensiones idénticas. Cualquier conversión aplicada al constructor externo ARRAY se propaga automáticamente a todos los constructores internos.

Los elementos del constructor de arrays multidimensionales pueden ser cualquier cosa que produzca un array del tipo adecuado, no solo una construcción de sub-ARRAY. Por ejemplo:

CREATE TABLE arr(f1 int[], f2 int[]);

INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);

SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;
                     array
------------------------------------------------
 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
(1 row)

Puedes construir un array vacío, pero dado que es imposible tener un array sin tipo, debes convertir explícitamente tu array vacío al tipo deseado. Por ejemplo:

SELECT ARRAY[]::integer[];
 array
-------
  {}
(1 row)

También es posible construir un array a partir de los resultados de una subconsulta. En esta forma, el constructor del array se escribe con la palabra clave ARRAY seguida de una subconsulta entre paréntesis (no entre corchetes). Por ejemplo:

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
                              array
------------------------------------------------------------------
 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412}
(1 row)

SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));
              array
----------------------------------
 {{1,2},{2,4},{3,6},{4,8},{5,10}}
(1 row)

La subconsulta debe devolver una sola columna. Si la columna de salida de la subconsulta es de un tipo que no es array, el array unidimensional resultante tendrá un elemento por cada fila en el resultado de la subconsulta, con un tipo de elemento que coincide con el de la columna de salida de la subconsulta. Si la columna de salida de la subconsulta es de tipo array, el resultado será un array del mismo tipo pero con una dimensión mayor; en este caso, todas las filas de la subconsulta deben producir arrays de dimensiones idénticas, de lo contrario el resultado no sería rectangular.

Los índices de un valor de array construido con ARRAY siempre comienzan con uno. Para obtener más información sobre los arrays, consulta la Section 8.15.

Un constructor de fila es una expresión que construye un valor de fila (también llamado valor compuesto) utilizando valores para sus campos miembros. Un constructor de fila consiste en la palabra clave ROW, un paréntesis izquierdo, cero o más expresiones (separadas por comas) para los valores de los campos de la fila y, finalmente, un paréntesis derecho. Por ejemplo:

SELECT ROW(1,2.5,'this is a test');

La palabra clave ROW es opcional cuando hay más de una expresión en la lista.

Un constructor de fila puede incluir la sintaxis valor_fila.*, la cual se expandirá a una lista de los elementos del valor de la fila, tal como ocurre cuando la sintaxis .* se utiliza en el nivel superior de una lista SELECT (consulta la Section 8.16.5). Por ejemplo, si la tabla t tiene las columnas f1 y f2, estas formas son equivalentes:

SELECT ROW(t.*, 42) FROM t;
SELECT ROW(t.f1, t.f2, 42) FROM t;

Por omisión, el valor creado por una expresión ROW es de un tipo de registro (record) anónimo. Si es necesario, se puede convertir a un tipo compuesto con nombre — ya sea el tipo de fila de una tabla o un tipo compuesto creado con CREATE TYPE AS. Se podría necesitar una conversión explícita para evitar la ambigüedad. Por ejemplo:

CREATE TABLE mytable(f1 int, f2 float, f3 text);

CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

-- No se requiere conversión ya que solo existe un getf1()
SELECT getf1(ROW(1,2.5,'this is a test'));
 getf1
-------
     1
(1 row)

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

-- Ahora necesitamos una conversión para indicar qué función llamar:
SELECT getf1(ROW(1,2.5,'this is a test'));
ERROR:  function getf1(record) is not unique

SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
 getf1
-------
     1
(1 row)

SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
 getf1
-------
    11
(1 row)

Los constructores de filas se pueden utilizar para construir valores compuestos que se almacenarán en una columna de tabla de tipo compuesto, o para pasarlos a una función que acepte un parámetro compuesto. Además, es posible probar filas utilizando los operadores de comparación estándar como se describe en la Section 9.2, comparar una fila con otra como se describe en la Section 9.25, y usarlos en relación con subconsultas, como se analiza en la Section 9.24.

El orden de evaluación de las subexpresiones no está definido. En particular, los valores de entrada de un operador o función no se evalúan necesariamente de izquierda a derecha o en ningún otro orden fijo.

Además, si el resultado de una expresión se puede determinar evaluando solo algunas partes de ella, entonces otras subexpresiones podrían no evaluarse en absoluto. Por ejemplo, si se escribe:

SELECT true OR somefunc();

entonces somefunc() (probablemente) no se llamaría en absoluto. Lo mismo ocurriría si se escribiera:

SELECT somefunc() OR true;

Ten en cuenta que esto no es lo mismo que el “cortocircuito” de izquierda a derecha de los operadores booleanos que se encuentra en algunos lenguajes de programación.

Como consecuencia de esto, no es aconsejable utilizar funciones con efectos secundarios como parte de expresiones complejas. Es particularmente peligroso confiar en los efectos secundarios o en el orden de evaluación en las cláusulas WHERE y HAVING, ya que esas cláusulas se vuelven a procesar exhaustivamente como parte del desarrollo de un plan de ejecución. Las expresiones booleanas (combinaciones de AND/OR/NOT) en esas cláusulas se pueden reorganizar de cualquier manera permitida por las leyes del álgebra de Boole.

Cuando sea esencial forzar el orden de evaluación, se puede usar una construcción CASE (consulta la Section 9.18). Por ejemplo, esta es una forma poco confiable de intentar evitar la división por cero en una cláusula WHERE:

SELECT ... WHERE x > 0 AND y/x > 1.5;

But this is safe:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Una construcción CASE utilizada de esta manera anulará los intentos de optimización, por lo que solo debe hacerse cuando sea necesario. (En este ejemplo en particular, sería mejor eludir el problema escribiendo y > 1.5*x en su lugar).

Sin embargo, CASE no es una solución mágica para tales problemas. Una limitación de la técnica ilustrada anteriormente es que no evita la evaluación temprana de subexpresiones constantes. Como se describe en la Section 36.7, las funciones y los operadores marcados como IMMUTABLE se pueden evaluar cuando se planifica la consulta, en lugar de cuando se ejecuta. Por lo tanto, por ejemplo:

SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;

es probable que resulte en un error de división por cero debido a que el planificador intenta simplificar la subexpresión constante, incluso si cada fila de la tabla tiene x > 0, de modo que el brazo ELSE nunca se alcanzaría en tiempo de ejecución.

Aunque ese ejemplo en particular pueda parecer absurdo, en las consultas ejecutadas dentro de funciones pueden darse casos relacionados que no involucran constantes de manera obvia, ya que los valores de los argumentos de la función y las variables locales pueden insertarse en las consultas como constantes con fines de planificación. Dentro de las funciones de PL/pgSQL, por ejemplo, usar una sentencia IF-THEN-ELSE para proteger un cálculo riesgoso es mucho más seguro que simplemente anidarlo en una expresión CASE.

Otra limitación del mismo tipo es que un CASE no puede evitar la evaluación de una expresión de agregación contenida en él, porque las expresiones de agregación se calculan antes de que se consideren otras expresiones en una lista SELECT o en una cláusula HAVING. Por ejemplo, la siguiente consulta puede causar un error de división por cero a pesar de que aparentemente se ha protegido contra él:

SELECT CASE WHEN min(employees) > 0
            THEN avg(expenses / employees)
       END
    FROM departments;

Los agregados min() y avg() se calculan simultáneamente sobre todas las filas de entrada, por lo que si alguna fila tiene employees igual a cero, el error de división por cero ocurrirá antes de que haya oportunidad de probar el resultado de min(). En su lugar, utiliza una cláusula WHERE o FILTER para evitar que las filas de entrada problemáticas lleguen a la función de agregación en primer lugar.