Cláusula WITH

La cláusula WITH te permite especificar una o más subconsultas que pueden ser referenciadas por nombre en la consulta principal. Las subconsultas actúan efectivamente como tablas o vistas temporales durante la ejecución de la consulta principal. Cada subconsulta puede ser una sentencia SELECT, TABLE, VALUES, INSERT, UPDATE, DELETE o MERGE. Al escribir una sentencia de modificación de datos (INSERT, UPDATE, DELETE o MERGE) en WITH, es habitual incluir una cláusula RETURNING. Es el resultado de RETURNING, y no la tabla subyacente que la sentencia modifica, lo que forma la tabla temporal que es leída por la consulta principal. Si se omite RETURNING, la sentencia se sigue ejecutando, pero no produce ninguna salida, por lo que no puede ser referenciada como una tabla por la consulta principal.

Se debe especificar un nombre (sin calificación de esquema) para cada consulta WITH. Opcionalmente, se puede especificar una lista de nombres de columnas; si se omite, los nombres de las columnas se infieren de la subconsulta.

Si se especifica RECURSIVE, se permite que una subconsulta SELECT se referencie a sí misma por nombre. Dicha subconsulta debe tener la forma

non_recursive_term UNION [ ALL | DISTINCT ] recursive_term

donde la autoreferencia recursiva debe aparecer en el lado derecho del UNION. Solo se permite una autoreferencia recursiva por consulta. No se admiten sentencias recursivas de modificación de datos, pero puedes utilizar los resultados de una consulta SELECT recursiva en una sentencia de modificación de datos. Consulta la Section 7.8 para ver un ejemplo.

Otro efecto de RECURSIVE es que las consultas WITH no necesitan estar ordenadas: una consulta puede referenciar a otra que esté más adelante en la lista. (Sin embargo, las referencias circulares o la recursividad mutua no están implementadas). Sin RECURSIVE, las consultas WITH solo pueden referenciar a consultas WITH compañeras que estén antes en la lista WITH.

Cuando hay múltiples consultas en la cláusula WITH, RECURSIVE debe escribirse una sola vez, inmediatamente después de WITH. Se aplica a todas las consultas en la cláusula WITH, aunque no tiene efecto en aquellas consultas que no utilizan recursividad ni referencias hacia adelante.

La cláusula opcional SEARCH computa una columna de secuencia de búsqueda que se puede utilizar para ordenar los resultados de una consulta recursiva en orden de anchura primero (breadth-first) o profundidad primero (depth-first). La lista de nombres de columnas proporcionada especifica la clave de fila que se utilizará para hacer el seguimiento de las filas visitadas. Se añadirá una columna llamada search_seq_col_name a la lista de columnas de resultados de la consulta WITH. Esta columna se puede ordenar en la consulta externa para lograr el ordenamiento correspondiente. Consulta la Section 7.8.2.1 para ver ejemplos.

La cláusula opcional CYCLE se utiliza para detectar bucles (ciclos) en consultas recursivas. La lista de nombres de columnas proporcionada especifica la clave de fila que se utilizará para hacer el seguimiento de las filas visitadas. Se añadirá una columna llamada cycle_mark_col_name a la lista de columnas de resultados de la consulta WITH. Esta columna se establecerá en cycle_mark_value cuando se haya detectado un ciclo, y en caso contrario en cycle_mark_default. Además, el procesamiento de la unión recursiva se detendrá cuando se haya detectado un ciclo. cycle_mark_value y cycle_mark_default deben ser constantes y coercibles a un tipo de datos común, y dicho tipo de datos debe tener un operador de desigualdad. (El estándar SQL requiere que sean constantes booleanas o cadenas de caracteres, pero PostgreSQL no lo exige). Por defecto, se utilizan TRUE y FALSE (de tipo boolean). Además, se añadirá una columna llamada cycle_path_col_name a la lista de columnas de resultados de la consulta WITH. Esta columna se utiliza internamente para el seguimiento de las filas visitadas. Consulta la Section 7.8.2.2 for examples.

Tanto la cláusula SEARCH como la cláusula CYCLE solo son válidas para consultas WITH recursivas. La with_query debe ser un UNION (o UNION ALL) de dos comandos SELECT (o equivalentes) sin UNIONs anidados. Si se utilizan ambas cláusulas, la columna añadida por la cláusula SEARCH aparece antes de las columnas añadidas por la cláusula CYCLE.

La consulta principal y las consultas WITH se ejecutan todas (nocionalmente) al mismo tiempo. Esto implica que los efectos de una sentencia de modificación de datos en WITH no se pueden ver desde otras partes de la consulta, a menos que sea leyendo su salida RETURNING. Si dos de estas sentencias de modificación de datos intentan modificar la misma fila, los resultados son indeterminados.

Una propiedad clave de las consultas WITH es que normalmente se evalúan una sola vez por ejecución de la consulta principal, incluso si la consulta principal se refiere a ellas más de una vez. En particular, se garantiza que las sentencias de modificación de datos se ejecutan una y solo una vez, independientemente de si la consulta principal lee toda, parte o ninguna de su salida.

Sin embargo, una consulta WITH se puede marcar como NOT MATERIALIZED para eliminar esta garantía. En ese caso, la consulta WITH se puede integrar (fold) en la consulta principal de forma muy similar a como si fuera una subconsulta SELECT simple en la cláusula FROM de la consulta principal. Esto provoca cómputos duplicados si la consulta principal se refiere a esa consulta WITH más de una vez; pero si cada uno de esos usos requiere solo unas pocas filas de la salida total de la consulta WITH, NOT MATERIALIZED puede proporcionar un ahorro neto al permitir que las consultas se optimicen conjuntamente. NOT MATERIALIZED se ignora si se asocia a una consulta WITH que es recursiva o que no está libre de efectos secundarios (es decir, que no es un SELECT simple que no contenga funciones volátiles).

Por defecto, una consulta WITH libre de efectos secundarios se integra en la consulta principal si se utiliza exactamente una vez en la cláusula FROM de la consulta principal. Esto permite la optimización conjunta de los dos niveles de consulta en situaciones donde eso debería ser semánticamente invisible. Sin embargo, dicha integración se puede evitar marcando la consulta WITH como MATERIALIZED. Eso podría ser útil, por ejemplo, si la consulta WITH se está utilizando como una barrera de optimización (optimization fence) para evitar que el planificador elija un mal plan. Las versiones de PostgreSQL anteriores a la v12 nunca realizaban dicha integración, por lo que las consultas escritas para versiones anteriores podrían depender de que WITH actúe como una barrera de optimización.

Cláusula FROM

La cláusula FROM especifica una o más tablas origen para el SELECT. Si se especifican múltiples orígenes, el resultado es el producto cartesiano (cross join) de todos los orígenes. Pero normalmente se añaden condiciones de calificación (a través de WHERE) para restringir las filas devueltas a un pequeño subconjunto del producto cartesiano.

La cláusula FROM puede contener los siguientes elementos:

Cláusula WHERE

La cláusula opcional WHERE tiene la forma general

WHERE condición

donde condición es cualquier expresión que se evalúe como un resultado de tipo boolean. Cualquier fila que no cumpla con esta condición será eliminada del resultado. Una fila cumple la condición si devuelve «true» cuando se sustituyen los valores reales de las filas en las referencias a variables.

Cláusula GROUP BY

La cláusula opcional GROUP BY tiene la forma general

GROUP BY [ ALL | DISTINCT ] elemento_de_agrupamiento [, ...]

GROUP BY condensará en una sola fila todas las filas seleccionadas que compartan los mismos valores para las expresiones agrupadas. Una expresión utilizada dentro de un elemento_de_agrupamiento puede ser el nombre de una columna de entrada, o el nombre o número ordinal de una columna de salida (elemento de la lista de SELECT), o una expresión arbitraria formada a partir de los valores de las columnas de entrada. En caso de ambigüedad, un nombre en GROUP BY se interpretará como el nombre de una columna de entrada en lugar del nombre de una columna de salida.

Si cualquiera de las opciones GROUPING SETS, ROLLUP o CUBE están presentes como elementos de agrupamiento, entonces la cláusula GROUP BY en su conjunto define un cierto número de conjuntos de agrupamiento (grouping sets) independientes. El efecto de esto es equivalente a construir un UNION ALL entre subconsultas con los conjuntos de agrupamiento individuales como sus cláusulas GROUP BY. La cláusula opcional DISTINCT elimina los conjuntos duplicados antes del procesamiento; no transforma el UNION ALL en un UNION DISTINCT. Para obtener más detalles sobre el manejo de los conjuntos de agrupamiento, consulta Section 7.2.4.

Las funciones de agregación, si se utiliza alguna, se calculan a través de todas las filas que componen cada grupo, produciendo un valor independiente para cada grupo. (Si hay funciones de agregación pero no hay cláusula GROUP BY, la consulta se trata como si tuviera un único grupo que comprende todas las filas seleccionadas). El conjunto de filas que se alimenta a cada función de agregación se puede filtrar aún más añadiendo una cláusula FILTER a la llamada de la función de agregación; consulta Section 4.2.7 para obtener más información. Cuando una cláusula FILTER está presente, solo las filas que coinciden con ella se incluyen en la entrada de esa función de agregación.

Cuando GROUP BY está presente, o hay funciones de agregación presentes, no es válido que las expresiones de la lista de SELECT hagan referencia a columnas no agrupadas, excepto dentro de las funciones de agregación o cuando la columna no agrupada depende funcionalmente de las columnas agrupadas, ya que de lo contrario habría más de un valor posible que devolver para una columna no agrupada. Existe una dependencia funcional si las columnas agrupadas (o un subconjunto de ellas) son la clave primaria de la tabla que contiene la columna no agrupada.

Ten en cuenta que todas las funciones de agregación se evalúan antes de evaluar cualquier expresión “escalar” en la cláusula HAVING o en la lista de SELECT. Esto significa que, por ejemplo, no se puede usar una expresión CASE para omitir la evaluación de una función de agregación; consulta Section 4.2.14.

Actualmente, no se pueden especificar FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE y FOR KEY SHARE con GROUP BY.

Cláusula HAVING

La cláusula opcional HAVING tiene la forma general

HAVING condición

donde condición es la misma que la especificada para la cláusula WHERE.

HAVING elimina las filas de grupo que no cumplen con la condición. HAVING es diferente de WHERE: WHERE filtra filas individuales antes de aplicar GROUP BY, mientras que HAVING filtra las filas de grupo creadas por GROUP BY. Cada columna referenciada en condición debe hacer referencia inequívoca a una columna de agrupamiento, a menos que la referencia aparezca dentro de una función de agregación o que la columna no agrupada sea dependiente funcionalmente de las columnas de agrupamiento.

La presencia de HAVING convierte una consulta en una consulta agrupada incluso si no hay una cláusula GROUP BY. Esto es lo mismo que ocurre cuando la consulta contiene funciones de agregación pero no hay cláusula GROUP BY. Se considera que todas las filas seleccionadas forman un único grupo, y la lista de SELECT y la cláusula HAVING solo pueden hacer referencia a columnas de la tabla desde el interior de funciones de agregación. Dicha consulta emitirá una sola fila si la condición de HAVING es verdadera, y cero filas si no lo es.

Actualmente, no se pueden especificar FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE y FOR KEY SHARE con HAVING.

Cláusula WINDOW

La cláusula opcional WINDOW tiene la forma general

WINDOW window_name AS ( window_definition ) [, ...]

donde window_name es un nombre al que se puede hacer referencia desde las cláusulas OVER o definiciones de ventana subsiguientes, y window_definition es

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

Si se especifica un existing_window_name, este debe referirse a una entrada anterior en la lista de WINDOW; la nueva ventana copia su cláusula de partición de esa entrada, así como su cláusula de ordenamiento si la hubiera. En este caso, la nueva ventana no puede especificar su propia cláusula PARTITION BY, y puede especificar ORDER BY solo si la ventana copiada no tiene una. La nueva ventana siempre utiliza su propia cláusula de marco; la ventana copiada no debe especificar una cláusula de marco.

Los elementos de la lista PARTITION BY se interpretan de manera muy similar a los elementos de una cláusula GROUP BY, excepto que siempre son expresiones simples y nunca el nombre o número de una columna de salida. Otra diferencia es que estas expresiones pueden contener llamadas a funciones de agregación, las cuales no están permitidas en una cláusula GROUP BY normal. Se permiten aquí porque el procesamiento de ventanas ocurre después del agrupamiento y la agregación.

Del mismo modo, los elementos de la lista ORDER BY se interpretan de manera muy similar a los elementos de una cláusula ORDER BY a nivel de sentencia, excepto que las expresiones siempre se toman como expresiones simples y nunca como el nombre o número de una columna de salida.

La cláusula opcional frame_clause define el marco de ventana (window frame) para las funciones de ventana que dependen del marco (no todas lo hacen). El marco de ventana es un conjunto de filas relacionadas para cada fila de la consulta (llamada la fila actual). La frame_clause puede ser una de

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

donde frame_start y frame_end pueden ser uno de

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

y frame_exclusion puede ser uno de

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

Si se omite frame_end, se establece por defecto en CURRENT ROW. Las restricciones son que frame_start no puede ser UNBOUNDED FOLLOWING, frame_end no puede ser UNBOUNDED PRECEDING, y la opción de frame_end no puede aparecer antes en la lista anterior de opciones de frame_start y frame_end que la opción de frame_start; por ejemplo RANGE BETWEEN CURRENT ROW AND offset PRECEDING no está permitido.

La opción de marco por defecto es RANGE UNBOUNDED PRECEDING, que es lo mismo que RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; establece que el marco contiene todas las filas desde el inicio de la partición hasta el último par (peer) de la fila actual (una fila que la cláusula ORDER BY de la ventana considera equivalente a la fila actual; todas las filas son pares si no hay ORDER BY). En general, UNBOUNDED PRECEDING significa que el marco comienza con la primera fila de la partición, y de manera similar UNBOUNDED FOLLOWING significa que el marco termina con la última fila de la partición, independientemente del modo RANGE, ROWS o GROUPS. En el modo ROWS, CURRENT ROW significa que el marco comienza o termina con la fila actual; pero en el modo RANGE o GROUPS significa que el marco comienza o termina con el primer o último par de la fila actual en el ordenamiento ORDER BY. Las opciones offset PRECEDING y offset FOLLOWING varían en significado según el modo de marco. En el modo ROWS, el offset es un entero que indica que el marco comienza o termina esa cantidad de filas antes o después de la fila actual. En el modo GROUPS, el offset es un entero que indica que el marco comienza o termina esa cantidad de grupos de pares antes o después del grupo de pares de la fila actual, donde un grupo de pares es un grupo de filas que son equivalentes según la cláusula ORDER BY de la ventana. En el modo RANGE, el uso de una opción offset requiere que haya exactamente una columna ORDER BY en la definición de la ventana. Entonces el marco contiene aquellas filas cuyo valor de columna de ordenamiento no es menor que (para PRECEDING) o mayor que (para FOLLOWING) el valor de columna de ordenamiento de la fila actual más/menos la expresión offset. En estos casos, el tipo de datos de la expresión offset depende del tipo de datos de la columna de ordenamiento. Para columnas de ordenamiento numéricas, normalmente es del mismo tipo que la columna de ordenamiento, pero para columnas de ordenamiento de fecha y hora (datetime) es un interval. En todos estos casos, el valor del offset debe ser no nulo y no negativo. Además, aunque el offset no tiene que ser una constante simple, no puede contener variables, funciones de agregación o funciones de ventana.

La opción frame_exclusion permite que las filas alrededor de la fila actual se excluyan del marco, incluso si se incluyeran de acuerdo con las opciones de inicio de marco y fin de marco. EXCLUDE CURRENT ROW excluye la fila actual del marco. EXCLUDE GROUP excluye la fila actual y sus pares de ordenamiento del marco. 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 defecto de no excluir la fila actual ni sus pares.

Ten en cuenta que el modo ROWS puede producir resultados impredecibles si el ordenamiento ORDER BY no ordena las filas de manera única. Los modos RANGE y GROUPS están diseñados para garantizar que las filas que son pares en el ordenamiento ORDER BY se traten de la misma manera: todas las filas de un grupo de pares determinado estarán en el marco o se excluirán de él.

El propósito de una cláusula WINDOW es especificar el comportamiento de las funciones de ventana que aparecen en la SELECT list o la cláusula ORDER BY de la consulta. Estas funciones pueden hacer referencia a las entradas de la cláusula WINDOW por su nombre en sus cláusulas OVER. Sin embargo, no es necesario hacer referencia a una entrada de la cláusula WINDOW en ningún lugar; si no se utiliza en la consulta, simplemente se ignora. Es posible utilizar funciones de ventana sin ninguna cláusula WINDOW en absoluto, ya que una llamada a una función de ventana puede especificar su definición de ventana directamente en su cláusula OVER. Sin embargo, la cláusula WINDOW ahorra tener que escribir de más cuando se necesita la misma definición de ventana para más de una función de ventana.

Actualmente, no se pueden especificar FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE y FOR KEY SHARE con WINDOW.

Las funciones de ventana se describen detalladamente en Section 3.5, Section 4.2.8 y Section 7.2.5.

Lista de SELECT

La lista de SELECT (entre las palabras clave SELECT y FROM) especifica las expresiones que forman las filas de salida de la sentencia SELECT. Las expresiones pueden hacer referencia (y normalmente lo hacen) a columnas calculadas en la cláusula FROM.

Al igual que en una tabla, cada columna de salida de un SELECT tiene un nombre. En un SELECT simple, este nombre solo se utiliza para etiquetar la columna para su visualización, pero cuando el SELECT es una subconsulta de una consulta más grande, la consulta más grande ve el nombre como el nombre de columna de la tabla virtual producida por la subconsulta. Para especificar el nombre que se utilizará para una columna de salida, escribe AS output_name después de la expresión de la columna. (Puedes omitir AS, pero solo si el nombre de salida deseado no coincide con ninguna palabra clave de PostgreSQL (ver Appendix C). Para protegerte de la posible adición futura de palabras clave, se recomienda que siempre escribas AS o encierres entre comillas dobles el nombre de salida). Si no especificas un nombre de columna, PostgreSQL elige uno automáticamente. Si la expresión de la columna es una referencia simple a una columna, el nombre elegido será el mismo que el nombre de esa columna. En casos más complejos, se puede utilizar el nombre de una función o tipo, o el sistema puede recurrir a un nombre generado como ?column?.

El nombre de una columna de salida se puede utilizar para hacer referencia al valor de la columna en las cláusulas ORDER BY y GROUP BY, pero no en las cláusulas WHERE o HAVING; allí debes escribir la expresión completa en su lugar.

En lugar de una expresión, se puede escribir * en la lista de salida como una abreviatura para todas las columnas de las filas seleccionadas. También puedes escribir table_name.* como una abreviatura para las columnas que provienen únicamente de esa tabla. En estos casos, no es posible especificar nuevos nombres con AS; los nombres de las columnas de salida serán los mismos que los nombres de las columnas de la tabla.

Según el estándar SQL, las expresiones de la lista de salida deben calcularse antes de aplicar DISTINCT, ORDER BY o LIMIT. Esto es obviamente necesario al usar DISTINCT, ya que de lo contrario no estaría claro qué valores se están haciendo distintos. Sin embargo, en muchos casos es conveniente si las expresiones de salida se calculan después de ORDER BY y LIMIT; particularmente si la lista de salida contiene funciones volátiles o costosas. Con ese comportamiento, el orden de evaluación de las funciones es más intuitivo y no habrá evaluaciones correspondientes a filas que nunca aparecen en la salida. PostgreSQL evaluará de manera efectiva las expresiones de salida después de ordenar y limitar, siempre y cuando esas expresiones no estén referenciadas en DISTINCT, ORDER BY o GROUP BY. (Como contraejemplo, SELECT f(x) FROM tab ORDER BY 1 claramente debe evaluar f(x) antes de ordenar). Las expresiones de salida que contienen funciones que devuelven conjuntos se evalúan de manera efectiva después de ordenar y antes de limitar, de modo que LIMIT actuará para cortar la salida de una función que devuelve un conjunto.

Cláusula DISTINCT

Si se especifica SELECT DISTINCT, se eliminan todas las filas duplicadas del conjunto de resultados (se mantiene una fila de cada grupo de duplicados). SELECT ALL especifica lo contrario: se mantienen todas las filas; ese es el comportamiento por defecto.

SELECT DISTINCT ON ( expression [, ...] ) mantiene solo la primera fila de cada conjunto de filas donde las expresiones dadas se evalúan como iguales. Las expresiones de DISTINCT ON se interpretan utilizando las mismas reglas que para ORDER BY (ver arriba). Ten en cuenta que la “primera fila” de cada conjunto es impredecible a menos que se utilice ORDER BY para garantizar que la fila deseada aparezca primero. Por ejemplo:

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

recupera el reporte meteorológico más reciente para cada ubicación. Pero si no hubiéramos usado ORDER BY para forzar el orden descendente de los valores de tiempo para cada ubicación, habríamos obtenido un reporte de un tiempo impredecible para cada ubicación.

La(s) expresión(es) DISTINCT ON deben coincidir con la(s) expresión(es) ORDER BY más a la izquierda. La cláusula ORDER BY normalmente contendrá expresión(es) adicional(es) que determinan la precedencia deseada de las filas dentro de cada grupo de DISTINCT ON.

Actualmente, no se pueden especificar FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE y FOR KEY SHARE con DISTINCT.

Cláusula UNION

La cláusula UNION tiene esta forma general:

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement es cualquier sentencia SELECT sin una cláusula ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE o FOR KEY SHARE. (ORDER BY y LIMIT se pueden adjuntar a una subexpresión si está encerrada entre paréntesis. Sin paréntesis, se considerará que estas cláusulas se aplican al resultado de la UNION, no a su expresión de entrada del lado derecho).

El operador UNION calcula la unión de conjuntos de las filas devueltas por las sentencias SELECT involucradas. Una fila está en la unión de conjuntos de dos conjuntos de resultados si aparece en al menos uno de los conjuntos de resultados. Las dos sentencias SELECT que representan los operandos directos de la UNION deben producir el mismo número de columnas, y las columnas correspondientes deben ser de tipos de datos compatibles.

El resultado de UNION no contiene filas duplicadas a menos que se especifique la opción ALL. ALL evita la eliminación de duplicados. (Por lo tanto, UNION ALL suele ser significativamente más rápido que UNION; usa ALL cuando puedas). Se puede escribir DISTINCT para especificar explícitamente el comportamiento por defecto de eliminar las filas duplicadas.

Los operadores UNION múltiples en la misma sentencia SELECT se evalúan de izquierda a derecha, a menos que se indique lo contrario mediante paréntesis.

Actualmente, no se pueden especificar FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE ni FOR KEY SHARE ni para un resultado de UNION ni para ninguna entrada de una UNION.

Cláusula INTERSECT

La cláusula INTERSECT tiene esta forma general:

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

select_statement es cualquier sentencia SELECT sin una cláusula ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE o FOR KEY SHARE.

El operador INTERSECT calcula la intersección de conjuntos de las filas devueltas por las sentencias SELECT involucradas. Una fila está en la intersección de dos conjuntos de resultados si aparece en ambos conjuntos de resultados.

El resultado de INTERSECT no contiene filas duplicadas a menos que se especifique la opción ALL. Con ALL, una fila que tiene m duplicados en la tabla izquierda y n duplicados en la tabla derecha aparecerá min(m,n) veces en el conjunto de resultados. Se puede escribir DISTINCT para especificar explícitamente el comportamiento por defecto de eliminar las filas duplicadas.

Los operadores INTERSECT múltiples en la misma sentencia SELECT se evalúan de izquierda a derecha, a menos que los paréntesis dicten lo contrario. INTERSECT se asocia con mayor prioridad que UNION. Es decir, A UNION B INTERSECT C se leerá como A UNION (B INTERSECT C).

Actualmente, no se pueden especificar FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE ni FOR KEY SHARE ni para un resultado de INTERSECT ni para ninguna entrada de un INTERSECT.

Cláusula EXCEPT

La cláusula EXCEPT tiene esta forma general:

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

select_statement es cualquier sentencia SELECT sin una cláusula ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE o FOR KEY SHARE.

El operador EXCEPT calcula el conjunto de filas que están en el resultado de la sentencia SELECT izquierda pero no en el resultado de la derecha.

El resultado de EXCEPT no contiene filas duplicadas a menos que se especifique la opción ALL. Con ALL, una fila que tiene m duplicados en la tabla izquierda y n duplicados en la tabla derecha aparecerá max(m-n,0) times en el conjunto de resultados. Se puede escribir DISTINCT para especificar explícitamente el comportamiento por defecto de eliminar las filas duplicadas.

Los operadores EXCEPT múltiples en la misma sentencia SELECT se evalúan de izquierda a derecha, a menos que los paréntesis dicten lo contrario. EXCEPT se asocia al mismo nivel que UNION.

Actualmente, no se pueden especificar FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE ni FOR KEY SHARE ni para un resultado de EXCEPT ni para ninguna entrada de un EXCEPT.

Cláusula ORDER BY

La cláusula opcional ORDER BY tiene esta forma general:

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

La cláusula ORDER BY hace que las filas del resultado se ordenen según las expresiones especificadas. Si dos filas son iguales según la expresión más a la izquierda, se comparan según la siguiente expresión y así sucesivamente. Si son iguales según todas las expresiones especificadas, se devuelven en un orden que depende de la implementación.

Cada expression puede ser el nombre o número ordinal de una columna de salida (elemento de la lista de SELECT), o puede ser una expresión arbitraria formada a partir de los valores de las columnas de entrada.

El número ordinal se refiere a la posición ordinal (de izquierda a derecha) de la columna de salida. Esta característica permite definir un ordenamiento sobre la base de una columna que no tiene un nombre único. Esto nunca es absolutamente necesario porque siempre es posible asignar un nombre a una columna de salida utilizando la cláusula AS.

También es posible utilizar expresiones arbitrarias en la cláusula ORDER BY, incluyendo columnas que no aparecen en la lista de salida de SELECT. Por lo tanto, la siguiente sentencia es válida:

SELECT name FROM distributors ORDER BY code;

Una limitación de esta característica es que una cláusula ORDER BY que se aplica al resultado de una cláusula UNION, INTERSECT o EXCEPT solo puede especificar el nombre o número de una columna de salida, no una expresión.

Si una expresión ORDER BY es un nombre simple que coincide tanto con el nombre de una columna de salida como con el de una columna de entrada, ORDER BY lo interpretará como el nombre de la columna de salida. Esto es lo contrario de la elección que tomará GROUP BY en la misma situación. Esta inconsistencia se hace para ser compatible con el estándar SQL.

Opcionalmente se puede añadir la palabra clave ASC (ascendente) o DESC (descendente) después de cualquier expresión en la cláusula ORDER BY. Si no se especifica, se asume ASC por defecto. Alternativamente, se puede especificar un nombre de operador de ordenamiento específico en la cláusula USING. Un operador de ordenamiento debe ser un miembro de menor o mayor de alguna familia de operadores de árbol B (B-tree). ASC suele ser equivalente a USING < y DESC suele ser equivalente a USING >. (Pero el creador de un tipo de datos definido por el usuario puede definir exactamente cuál es el orden de ordenamiento por defecto, y este podría corresponder a operadores con otros nombres).

Si se especifica NULLS LAST, los valores nulos se ordenan después de todos los valores no nulos; si se especifica NULLS FIRST, los valores nulos se ordenan antes de todos los valores no nulos. Si no se especifica ninguno, el comportamiento por defecto es NULLS LAST cuando se especifica o implícita ASC, y NULLS FIRST cuando se especifica DESC (por lo tanto, el comportamiento por defecto es actuar como si los nulos fueran mayores que los no nulos). Cuando se especifica USING, el orden por defecto de los nulos depende de si el operador es un operador de menor o mayor.

Ten en cuenta que las opciones de ordenamiento se aplican únicamente a la expresión a la que siguen; por ejemplo, ORDER BY x, y DESC no significa lo mismo que ORDER BY x DESC, y DESC.

Los datos de cadenas de caracteres se ordenan según la colación (collation) que se aplica a la columna que se está ordenando. Eso se puede anular si es necesario incluyendo una cláusula COLLATE en la expression, por ejemplo ORDER BY mycolumn COLLATE "en_US". Para obtener más información, consulta Section 4.2.10 and Section 23.2.

Cláusula LIMIT

La cláusula LIMIT consta de dos subcláusulas independientes:

LIMIT { count | ALL }
OFFSET start

El parámetro count especifica el número máximo de filas a devolver, mientras que start especifica el número de filas que se deben omitir antes de comenzar a devolver filas. Cuando se especifican ambos, se omiten las filas de start antes de comenzar a contar las filas de count a devolver.

Si la expresión count se evalúa como NULL, se trata como LIMIT ALL, es decir, sin límite. Si start se evalúa como NULL, se trata de la misma manera que OFFSET 0.

SQL:2008 introdujo una sintaxis diferente para lograr el mismo resultado, que PostgreSQL también soporta. Es:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

En esta sintaxis, el estándar requiere que el valor de start o count sea una constante literal, un parámetro o un nombre de variable; como una extensión de PostgreSQL, se permiten otras expresiones, pero generalmente deberán estar encerradas entre paréntesis para evitar ambigüedades. Si se omite count en una cláusula FETCH, su valor por defecto es 1. La opción WITH TIES se utiliza para devolver cualquier fila adicional que empate por el último lugar en el conjunto de resultados según la cláusula ORDER BY; ORDER BY es obligatorio en este caso, y no se permite SKIP LOCKED. Las palabras ROW y ROWS, así como FIRST and NEXT, son palabras de adorno que no influyen en los efectos de estas cláusulas. Según el estándar, la cláusula OFFSET debe ir antes de la cláusula FETCH si ambas están presentes; pero PostgreSQL es más flexible y permite cualquier orden.

Al utilizar LIMIT, es una buena idea usar una cláusula ORDER BY que restrinja las filas del resultado a un orden único. De lo contrario, obtendrás un subconjunto impredecible de las filas de la consulta — podrías estar solicitando desde la décima hasta la vigésima fila, pero ¿la décima hasta la vigésima en qué orden? No conoces el orden a menos que especifiques ORDER BY.

El planificador de consultas tiene en cuenta LIMIT al generar un plan de consulta, por lo que es muy probable que obtengas diferentes planes (que producen diferentes órdenes de filas) dependiendo de lo que uses para LIMIT y OFFSET. Por lo tanto, el uso de diferentes valores de LIMIT/OFFSET para seleccionar diferentes subconjuntos de un resultado de consulta dará como resultado datos inconsistentes a menos que fuerces un orden de resultado predecible con ORDER BY. Esto no es un error; es una consecuencia inherente del hecho de que SQL no promete entregar los resultados de una consulta en ningún orden en particular a menos que se utilice ORDER BY para restringir el orden.

Incluso es posible que ejecuciones repetidas de la misma consulta LIMIT devuelvan subconjuntos de filas diferentes de una tabla, si no hay un ORDER BY para forzar la selección de un subconjunto determinista. Nuevamente, esto no es un error; el determinismo de los resultados simplemente no está garantizado en tal caso.

La cláusula de bloqueo (Locking Clause)

FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE y FOR KEY SHARE son cláusulas de bloqueo (locking clauses); afectan el modo en que SELECT bloquea las filas a medida que se obtienen de la tabla.

La cláusula de bloqueo tiene la forma general

FOR lock_strength [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ]

donde lock_strength puede ser uno de

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE

from_reference debe ser un alias de tabla o un table_name no oculto referenciado en la cláusula FROM. Para obtener más información sobre cada modo de bloqueo a nivel de fila, consulta Section 13.3.2.

Para evitar que la operación espere a que otras transacciones se confirmen, usa la opción NOWAIT o SKIP LOCKED. Con NOWAIT, la sentencia informa un error, en lugar de esperar, si una fila seleccionada no se puede bloquear de inmediato. Con SKIP LOCKED, se omiten las filas seleccionadas que no se puedan bloquear de inmediato. Omitir las filas bloqueadas proporciona una vista inconsistente de los datos, por lo que esto no es adecuado para trabajos de propósito general, pero se puede usar para evitar la contención de bloqueos con múltiples consumidores que acceden a una tabla similar a una cola. Ten en cuenta que NOWAIT y SKIP LOCKED se aplican únicamente a los bloqueos a nivel de fila — el bloqueo a nivel de tabla ROW SHARE requerido se sigue adquiriendo de la manera habitual (ver Chapter 13). Puedes usar LOCK con la opción NOWAIT primero, si necesitas adquirir el bloqueo a nivel de tabla sin esperar.

Si se nombran tablas específicas en una cláusula de bloqueo, entonces solo se bloquean las filas provenientes de esas tablas; cualquier otra tabla utilizada en el SELECT simplemente se lee de la manera habitual. Una cláusula de bloqueo sin una lista de tablas afecta a todas las tablas utilizadas en la sentencia. Si una cláusula de bloqueo se aplica a una vista o subconsulta, afecta a todas las tablas utilizadas en la vista o subconsulta. Sin embargo, estas cláusulas no se aplican a las consultas WITH referenciadas por la consulta principal. Si deseas que ocurra un bloqueo de filas dentro de una consulta WITH, especifica una cláusula de bloqueo dentro de la consulta WITH.

Se pueden escribir múltiples cláusulas de bloqueo si es necesario especificar un comportamiento de bloqueo diferente para diferentes tablas. Si la misma tabla es mencionada (o afectada implícitamente) por más de una cláusula de bloqueo, entonces se procesa como si solo se hubiera especificado en la más fuerte. Del mismo modo, una tabla se procesa como NOWAIT si eso se especifica en alguna de las cláusulas que la afectan. De lo contrario, se procesa como SKIP LOCKED si eso se especifica en alguna de las cláusulas que la afectan.

Las cláusulas de bloqueo no se pueden utilizar en contextos donde las filas devueltas no puedan identificarse claramente con filas de tabla individuales; por ejemplo, no se pueden usar con agregación.

Cuando una cláusula de bloqueo aparece en el nivel superior de una consulta SELECT, las filas que se bloquean son exactamente aquellas que devuelve la consulta; en el caso de una consulta de unión (join), las filas bloqueadas son aquellas que contribuyen a las filas de unión devueltas. Además, se bloquearán las filas que cumplan con las condiciones de la consulta a partir de la instantánea (snapshot) de la consulta, aunque no se devolverán si se actualizaron después de la instantánea y ya no cumplen con las condiciones de la consulta. Si se utiliza LIMIT, el bloqueo se detiene una vez que se han devuelto suficientes filas para cumplir con el límite (pero ten en cuenta que las filas omitidas por OFFSET se bloquearán). Del mismo modo, si se utiliza una cláusula de bloqueo en la consulta de un cursor, solo se bloquearán las filas realmente recuperadas (fetched) o sobrepasadas por el cursor.

Cuando una cláusula de bloqueo aparece en un sub-SELECT, las filas bloqueadas son aquellas devueltas a la consulta externa por la subconsulta. Esto podría involucrar menos filas de las que sugeriría la inspección de la subconsulta por sí sola, ya que las condiciones de la consulta externa podrían usarse para optimizar la ejecución de la subconsulta. Por ejemplo,

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;

bloqueará únicamente las filas que tengan col1 = 5, aunque esa condición no esté textualmente dentro de la subconsulta.

Las versiones anteriores no preservaban un bloqueo que es promovido por un punto de salvaguarda (savepoint) posterior. Por ejemplo, este código:

BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;

fallaría al preservar el bloqueo FOR UPDATE después del ROLLBACK TO. Esto se ha solucionado en la versión 9.3.

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;

Comando TABLE

El comando

TABLE name

es equivalente a

SELECT * FROM name

Se puede usar como un comando de nivel superior o como una variante de sintaxis para ahorrar espacio en partes de consultas complejas. Solo las cláusulas de bloqueo WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH y FOR se pueden usar con TABLE; la cláusula WHERE y cualquier forma de agregación no se pueden utilizar.

Cláusulas FROM omitidas

PostgreSQL permite omitir la cláusula FROM. Esto tiene un uso sencillo para calcular los resultados de expresiones simples:

SELECT 2+2;

 ?column?
----------
        4

Algunas otras bases de datos SQL no pueden hacer esto a menos que introduzcan una tabla ficticia de una sola fila desde la cual realizar el SELECT.

Listas de SELECT vacías

La lista de expresiones de salida después de SELECT puede estar vacía, produciendo una tabla de resultados de cero columnas. Esta no es una sintaxis válida según el estándar SQL. PostgreSQL lo permite para ser consistente con la posibilidad de permitir tablas de cero columnas. Sin embargo, no se permite una lista vacía cuando se utiliza DISTINCT.

Omisión de la palabra clave AS

En el estándar SQL, la palabra clave opcional AS se puede omitir antes del nombre de una columna de salida siempre que el nuevo nombre de columna sea un nombre de columna válido (es decir, que no coincida con ninguna palabra clave reservada). PostgreSQL es ligeramente más restrictivo: se requiere AS si el nuevo nombre de la columna coincide con cualquier palabra clave, reservada o no. La práctica recomendada es usar AS o escribir entre comillas dobles los nombres de las columnas de salida, para evitar cualquier posible conflicto con futuras adiciones de palabras clave.

En los elementos de FROM, tanto el estándar como PostgreSQL permiten omitir AS antes de un alias que sea una palabra clave no reservada. Pero esto no es práctico para los nombres de las columnas de salida debido a ambigüedades sintácticas.

Omisión de alias de sub-SELECT en FROM

Según el estándar SQL, un sub-SELECT en la lista FROM debe tener un alias. En PostgreSQL, este alias se puede omitir.

ONLY y herencia

El estándar SQL requiere paréntesis alrededor del nombre de la tabla al escribir ONLY, por ejemplo SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... PostgreSQL considera que estos paréntesis son opcionales.

PostgreSQL permite escribir un * al final para especificar explícitamente el comportamiento no-ONLY de incluir las tablas hijas. El estándar no permite esto.

(Estos puntos se aplican por igual a todos los comandos SQL que soportan la opción ONLY).

Restricciones de la cláusula TABLESAMPLE

La cláusula TABLESAMPLE se acepta actualmente solo en tablas normales y vistas materializadas. Según el estándar SQL debería ser posible aplicarla a cualquier elemento de FROM.

Llamadas a funciones en FROM

PostgreSQL permite escribir una llamada a una función directamente como un miembro de la lista FROM. En el estándar SQL sería necesario envolver dicha llamada a función en un sub-SELECT; es decir, la sintaxis FROM func(...) alias es aproximadamente equivalente a FROM LATERAL (SELECT func(...)) alias. Ten en cuenta que LATERAL se considera implícito; esto se debe a que el estándar requiere la semántica de LATERAL para un elemento UNNEST() en FROM. PostgreSQL trata a UNNEST() de la misma manera que a otras funciones que devuelven conjuntos.

Espacio de nombres disponible para GROUP BY y ORDER BY

En el estándar SQL-92, una cláusula ORDER BY solo puede usar nombres o números de columnas de salida, mientras que una cláusula GROUP BY solo puede usar expresiones basadas en nombres de columnas de entrada. PostgreSQL extiende cada una de estas cláusulas para permitir también la otra opción (pero utiliza la interpretación del estándar si hay ambigüedad). PostgreSQL también permite que ambas cláusulas especifiquen expresiones arbitrarias. Ten en cuenta que los nombres que aparecen en una expresión siempre se tomarán como nombres de columnas de entrada, no como nombres de columnas de salida.

SQL:1999 y posteriores utilizan una definición ligeramente diferente que no es completamente compatible hacia adelante con SQL-92. Sin embargo, en la mayoría de los casos PostgreSQL interpretará una expresión de ORDER BY o GROUP BY de la misma manera que lo hace SQL:1999.

Dependencias funcionales

PostgreSQL reconoce la dependencia funcional (permitiendo omitir columnas de GROUP BY) solo cuando la clave primaria de una tabla está incluida en la lista de GROUP BY. El estándar SQL especifica condiciones adicionales que deberían ser reconocidas.

LIMIT y OFFSET

Las cláusulas LIMIT y OFFSET son una sintaxis específica de PostgreSQL, también utilizada por MySQL. El estándar SQL:2008 ha introducido las cláusulas OFFSET ... FETCH {FIRST|NEXT} ... para la misma funcionalidad, como se muestra arriba en LIMIT Clause. Esta sintaxis también es utilizada por IBM DB2. (Las aplicaciones escritas para Oracle frecuentemente utilizan una solución alternativa que involucra la columna rownum generada automáticamente, la cual no está disponible en PostgreSQL, para implementar los efectos de estas cláusulas).

FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE

Aunque FOR UPDATE appears en el estándar SQL, el estándar lo permite solo como una opción de DECLARE CURSOR. PostgreSQL lo permite en cualquier consulta SELECT así como en sub-SELECTs, pero esto es una extensión. Las variantes FOR NO KEY UPDATE, FOR SHARE y FOR KEY SHARE, así como las opciones NOWAIT y SKIP LOCKED, no aparecen en el estándar.

Sentencias de modificación de datos en WITH

PostgreSQL permite utilizar INSERT, UPDATE, DELETE y MERGE como consultas WITH. Esto no se encuentra en el estándar SQL.

Cláusulas no estándar

DISTINCT ON ( ... ) es una extensión del estándar SQL.

ROWS FROM( ... ) es una extensión del estándar SQL.

Las opciones MATERIALIZED y NOT MATERIALIZED de WITH son extensiones del estándar SQL.