Cláusula WITH

A cláusula WITH permite que se especifique uma ou mais subconsultas, que podem ser referenciadas pelo nome na consulta primária. As subconsultas atuam efetivamente como tabelas ou visões temporárias durante a consulta principal. Cada subconsulta pode ser um comando SELECT, TABLE, VALUES, INSERT, UPDATE, DELETE ou MERGE. Ao escrever um comando de modificação de dados (INSERT, UPDATE, DELETE ou MERGE) na cláusula WITH, é comum incluir a cláusula RETURNING. É a saída da cláusula RETURNING, e não a tabela subjacente que o comando modifica, que forma a tabela temporária lida pela consulta primária. Se for omitida a cláusula RETURNING, o comando ainda assim será executado, mas não produzirá saída, portanto não poderá ser referenciado como uma tabela pela consulta primária.

Deve ser especificado um nome (sem qualificação de esquema) para cada consulta da cláusula WITH. Opcionalmente, pode ser especificada uma lista de nomes de colunas; se for omitido, os nomes das colunas serão inferidos da subconsulta.

Se for especificado RECURSIVE, é permitido que a subconsulta SELECT faça referência a si mesma pelo nome. Esta subconsulta deve ter o formato

termo_não_recursivo UNION [ ALL | DISTINCT ] termo_recursivo

onde a autorreferência recursiva deve aparecer no lado direito do operador UNION. É permitida apenas uma autorreferência recursiva por consulta. Não há suporte para comandos de modificação de dados recursivos, mas podem ser usados os resultados da consulta SELECT recursiva em um comando de modificação de dados. Veja Consultas WITH (Expressões de tabela comuns) para obter um exemplo.

Outro efeito de RECURSIVE é que as consultas WITH não precisam ser ordenadas: a consulta pode referir-se a outra que esteja posteriormente na lista. (Entretanto, referências circulares, ou recursão mútua, não estão implementadas.) Sem RECURSIVE, as consultas WITH só podem referir-se a outras consultas WITH que estão anteriormente na lista WITH.

Quando houver várias consultas na cláusula WITH, RECURSIVE deve ser escrito apenas uma vez, imediatamente após a cláusula WITH. Isto se aplica a todas as consultas na cláusula WITH, embora não afete as consultas que não usam recursão ou referências diretas.

A cláusula opcional SEARCH computa uma coluna de sequência de procura, que pode ser usada para ordenar os resultados de uma consulta recursiva em ordem de largura ou profundidade. A lista de nomes de coluna fornecida especifica a chave de linha que deve ser usada para controlar as linhas visitadas. Uma coluna chamada search_seq_col_name será adicionada à lista de colunas de resultados da consulta WITH. Esta coluna pode ser ordenada na consulta externa para obter a respectiva ordenação. Veja Ordem de procura para obter exemplos.

A cláusula opcional CYCLE é usada para detectar ciclos em consultas recursivas. A lista de nomes de coluna fornecida especifica a chave de linha que deve ser usada para controlar as linhas visitadas. Uma coluna chamada cycle_mark_col_name será adicionada à lista de colunas de resultados da consulta WITH. Esta coluna será definida como cycle_mark_value quando for detectado um ciclo, caso contrário, será cycle_mark_default. Além disso, o processamento da união recursiva será interrompido quando for detectado um ciclo. cycle_mark_value e cycle_mark_default devem ser constantes, e devem ser coercíveis para um tipo de dados comum, e o tipo de dados deve ter um operador de desigualdade. (O padrão SQL exige que sejam constantes booleanas, ou cadeias de caracteres, mas o PostgreSQL não exige isto.) Por padrão, são usados TRUE e FALSE (do tipo boolean). Além disso, será adicionada uma coluna chamada cycle_path_col_name à lista de colunas de resultados da consulta WITH. Esta coluna é usada internamente para rastrear as linhas visitadas. Veja Detecção de ciclo para obter exemplos.

As cláusulas SEARCH e CYCLE são válidas apenas para consultas WITH recursivas. A consulta_WITH deve ser uma UNION (ou UNION ALL) de dois comandos SELECT (ou equivalentes) (e não UNIONs aninhadas). Se as duas cláusulas forem usadas, a coluna adicionada pela cláusula SEARCH aparecerá antes das colunas adicionadas pela cláusula CYCLE.

A consulta primária e as consultas WITH são todas (conceitualmente) executadas ao mesmo tempo. Isto implica que os efeitos de um comando de modificação de dados na cláusula WITH não podem ser vistos de outras partes da consulta, exceto pela leitura de sua saída RETURNING. Se dois desses comandos modificadores de dados tentarem modificar a mesma linha, o resultado será indeterminado.

Uma propriedade chave das consultas WITH, é que elas são normalmente avaliadas apenas uma vez por execução da consulta primária, mesmo que a consulta primária se refira a elas mais de uma vez. Em particular, os comandos de modificação de dados são garantidos de serem executados uma vez, e apenas uma vez, independentemente da consulta primária ler toda ou parte de suas saídas.

Entretanto, a consulta WITH pode ser marcada como NOT MATERIALIZED para remover esta garantia. Neste caso, a consulta WITH pode ser mesclada na consulta primária como se fosse um simples sub-SELECT na cláusula FROM da consulta primária. Isto irá resultar em computações duplicadas, se a consulta principal se referir a esta consulta WITH mais de uma vez; mas se cada uso exigir apenas algumas linhas da saída total da consulta WITH, então NOT MATERIALIZED pode produzir um ganho, permitindo que as consultas sejam otimizadas em conjunto. A cláusula NOT MATERIALIZED é ignorada se for anexada a uma consulta WITH recursiva, ou que não é livre de efeitos colaterais (ou seja, não é um SELECT simples sem funções voláteis).

Por padrão, uma consulta WITH livre de efeitos colaterais é incluída na consulta primária se for usada apenas uma vez na cláusula FROM da consulta primária. Isto permite a otimização conjunta dos dois níveis de consulta em situações em que isto deveria ser semanticamente invisível. Entretanto, esta inclusão pode ser evitada marcando a consulta WITH como MATERIALIZED. Isto pode ser útil, por exemplo, se a consulta WITH estiver sendo usada como barreira de otimização para evitar que o planejador escolha um plano ruim. As versões do PostgreSQL anteriores à 12 nunca fizeram esta inclusão, portanto, as consultas escritas para versões mais antigas podem depender de WITH para atuar como barreira de otimização.

Veja Consultas WITH (Expressões de tabela comuns) para obter informações adicionais.

Cláusula FROM

A cláusula FROM especifica uma ou mais tabelas de origem para o comando SELECT. Se forem especificadas várias origens, o resultado será o produto cartesiano (junção cruzada) de todas as origens. Mas são geralmente adicionadas condições de qualificação (via cláusula WHERE), para restringir as linhas retornadas a um pequeno subconjunto do produto cartesiano.

A cláusula FROM pode conter os seguintes elementos:

Cláusula WHERE

A cláusula opcional WHERE tem a forma geral

WHERE condição

onde condição é qualquer expressão que produz um resultado do tipo de dados boolean. Qualquer linha que não satisfaça esta condição será eliminada da saída. Uma linha satisfaz a condição se retornar verdade quando os valores da linha forem substituídos por qualquer referência de variável.

Cláusula GROUP BY

A cláusula opcional GROUP BY tem a forma geral

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

A cláusula GROUP BY condensa em uma única linha todas as linhas selecionadas que compartilham os mesmos valores para as expressões de agrupamento. Uma expressão usada em um elemento_de_agrupamento pode ser o nome de uma coluna de entrada, ou o nome ou número ordinal de uma coluna de saída (item da lista SELECT), ou uma expressão arbitrária formada a partir de valores de coluna de entrada. Em caso de ambiguidade, um nome em GROUP BY será interpretado como um nome de coluna de entrada, em vez de um nome de coluna de saída.

Se estiver presente uma das cláusulas GROUPING SETS, ROLLUP ou CUBE como elementos de agrupamento, então a cláusula GROUP BY como um todo define um certo número de conjuntos de agrupamentos independentes. O efeito disso é equivalente a construir um UNION ALL entre as subconsultas com os conjuntos de agrupamento individuais como suas cláusulas GROUP BY. A cláusula opcional DISTINCT remove conjuntos duplicados antes do processamento; não transforma o UNION ALL em um UNION DISTINCT. Para mais detalhes sobre o tratamento de conjuntos de agrupamento, veja a GROUPING SETS, CUBE e ROLLUP.

As funções de agregação, se forem usadas, serão calculadas em todas as linhas que compõem cada grupo, produzindo um valor separado para cada grupo. (Se houver funções de agregação, mas nenhuma cláusula GROUP BY, a consulta será tratada como tendo um único grupo compreendendo todas as linhas selecionadas.) O conjunto de linhas alimentadas para cada função de agregação pode ser filtrado adicionalmente anexando uma cláusula FILTER à chamada da função de agregação; veja a Expressões de agregação para obter mais informações. Quando está presente uma cláusula FILTER, apenas as linhas que correspondem a ela são incluídas na entrada dessa função de agregação.

Quando está presente a cláusula GROUP BY, ou qualquer função de agregação, não é válido para as expressões na lista do SELECT fazerem referência a colunas não agrupadas, exceto em funções de agregação, ou quando a coluna não agrupada depende funcionalmente das colunas agrupadas, caso contrário, haveria mais de um valor possível para retornar para uma coluna não agrupada. Uma dependência funcional existe se as colunas agrupadas (ou um subconjunto delas) forem a chave primária da tabela que contém a coluna não agrupada.

Lembre-se de que todas as funções de agregação são avaliadas antes de avaliar qualquer expressão “escalar” na cláusula HAVING ou na lista SELECT. Isto significa que, por exemplo, uma expressão CASE não pode ser usada para ignorar a avaliação de uma função de agregação; veja a Regras para avaliação de expressão para obter mais informações.

No momento, não pode ser especificado FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE e FOR KEY SHARE com GROUP BY.

Cláusula HAVING

A cláusula opcional HAVING tem a forma geral

HAVING condição

onde condição é a mesma especificada para a cláusula WHERE.

A cláusula HAVING elimina linhas de grupo que não atendem à condição. HAVING é diferente de WHERE: WHERE filtra linhas individuais antes da aplicação de GROUP BY, enquanto HAVING filtra linhas agrupadas criadas por GROUP BY. Cada coluna referenciada na condição deve referenciar sem ambiguidade uma coluna de agrupamento, a menos que a referência apareça em uma função de agregação, ou a coluna não agrupada seja funcionalmente dependente das colunas de agrupamento.

A presença de HAVING transforma a consulta em uma consulta agrupada, mesmo não havendo a cláusula GROUP BY. Isto é o mesmo que acontece quando a consulta contém funções de agregação, mas nenhuma cláusula GROUP BY. Todas as linhas selecionadas são consideradas para formar um único grupo, e a lista SELECT e a cláusula HAVING só podem fazer referência a colunas de tabela de funções de agregação. Esta consulta emite uma única linha se a condição HAVING for verdadeira, e zero linhas se não for verdadeira.

No momento, não pode ser especificado FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE e FOR KEY SHARE com a cláusula HAVING.

Cláusula WINDOW

A cláusula opcional WINDOW tem a forma geral

WINDOW nome_da_janela AS ( definição_da_janela ) [, ...]

onde nome_da_janela é um nome que pode ser referenciado em cláusulas OVER ou definições de janela subsequentes, e definição_da_janela é

[ nome_de_janela_existente ]
[ PARTITION BY expressão [, ...] ]
[ ORDER BY expressão [ ASC | DESC | USING operador ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ cláusula_de_quadro ]

Se for especificado um nome_de_janela_existente, ele deverá se referir a uma entrada anterior na lista da cláusula WINDOW; a nova janela copia sua cláusula de particionamento dessa entrada, bem como sua cláusula de ordenação, se houver. Neste caso, a nova janela não pode especificar sua própria cláusula PARTITION BY, e pode especificar ORDER BY somente se a janela copiada não tiver uma. A nova janela sempre usa sua própria cláusula de quadro; a janela copiada não deve especificar uma cláusula de quadro.

Os elementos da lista PARTITION BY são interpretados da mesma forma que os elementos de uma cláusula GROUP BY, exceto por serem sempre expressões simples, e nunca o nome ou número de uma coluna de saída. Outra diferença é que estas expressões podem conter chamadas de funções de agregação, que não são permitidas em uma cláusula GROUP BY regular. Elas são permitidos aqui, porque o janelamento ocorre após o agrupamento e a agregação.

De forma semelhante, os elementos da lista da cláusula ORDER BY são interpretados da mesma forma que os elementos no nível de comando da cláusula ORDER BY, exceto que as expressões são sempre tomadas como expressões simples, e nunca o nome ou o número de uma coluna de saída.

A cláusula_de_quadro opcional define o quadro da janela para funções de janela que dependem do quadro (nem todas dependem). O quadro da janela é um conjunto de linhas relacionadas para cada linha da consulta (chamada linha corrente). A cláusula_de_quadro pode ser uma entre

{ RANGE | ROWS | GROUPS } início_de_quadro [ exclusão_de_quadro ]
{ RANGE | ROWS | GROUPS } BETWEEN início_de_quadro AND fim_de_quadro [ exclusão_de_quadro ]

onde início_de_quadro e fim_de_quadro pode ser um entre

UNBOUNDED PRECEDING
deslocamento PRECEDING
CURRENT ROW
deslocamento FOLLOWING
UNBOUNDED FOLLOWING

e exclusão_de_quadro pode ser um entre

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

Se fim_de_quadro for omitido, o padrão é CURRENT ROW. As restrições são que início_de_quadro não pode ser UNBOUNDED FOLLOWING, fim_de_quadro não pode ser UNBOUNDED PRECEDING, e a escolha de fim_de_quadro não pode aparecer antes na lista acima das opções de início_de_quadro e fim_de_quadro que a escolha de início_de_quadro — por exemplo, RANGE BETWEEN CURRENT ROW AND deslocamento PRECEDING não é permitido.

A opção de enquadramento padrão é RANGE UNBOUNDED PRECEDING, que é o mesmo que RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; isto define o quadro como sendo todas as linhas desde o início da partição até o último par da linha corrente (uma linha que a cláusula ORDER BY da janela considera equivalente à linha corrente; todas as linhas serão pares se não houver ORDER BY). Em geral, UNBOUNDED PRECEDING significa que o quadro começa com a primeira linha da partição, e analogamente UNBOUNDED FOLLOWING significa que o quadro termina com a última linha da partição, independentemente do modo RANGE, ROWS ou GROUPS. No modo ROWS, CURRENT ROW significa que o quadro começa ou termina com a linha corrente; mas no modo RANGE ou GROUPS, significa que o quadro começa ou termina com o primeiro ou último par da linha corrente na ordem estabelecida por ORDER BY. As opções deslocamento PRECEDING e deslocamento FOLLOWING variam de significado dependendo do modo de quadro. No modo ROWS, o deslocamento é um número inteiro que indica que o quadro começa, ou termina, tantas linhas antes ou depois da linha corrente; No modo GROUPS, o deslocamento é um número inteiro indicando que o quadro começa, ou termina, com tantos grupos de pares antes ou depois do grupo de pares da linha corrente, onde um grupo de pares é um grupo de linhas que são equivalentes segundo a cláusula ORDER BY da janela; No modo RANGE, o uso da opção deslocamento requer que haja exatamente uma coluna ORDER BY na definição da janela. Então, o quadro contém as linhas cujo valor da coluna de ordenação não é inferior ao deslocamento (para PRECEDING) ou superior (para FOLLOWING) ao valor da coluna de ordenação da linha corrente. Nesses casos, o tipo de dados da expressão deslocamento depende do tipo de dados da coluna de ordenação. Para colunas de ordenação numérica, é normalmente do mesmo tipo de dados que a coluna de ordenação, mas para colunas de ordenação por data e hora é do tipo de dados interval. Em todos estes casos, o valor do deslocamento deve ser não nulo e não negativo. Além disso, embora o deslocamento não precise ser uma constante simples, não pode conter variáveis, funções de agregação, ou funções de janela.

A opção exclusão_de_quadro permite que as linhas ao redor da linha corrente sejam excluídas do quadro, mesmo que pudessem ser incluídas segundo as opções de início e fim do quadro. A opção EXCLUDE CURRENT ROW exclui a linha corrente do quadro. A opção EXCLUDE GROUP exclui a linha corrente e seus pares de ordenação do quadro. A opção EXCLUDE TIES exclui quaisquer pares da linha corrente do quadro, mas não a própria linha corrente. A opção EXCLUDE NO OTHERS simplesmente especifica explicitamente o comportamento padrão de não excluir a linha corrente ou seus pares.

Tenha cuidado, porque o modo ROWS poderá produzir resultados imprevisíveis se a ordem estabelecida por ORDER BY não ordenar as linhas de modo único. Os modos RANGE e GROUPS são projetados para garantir que as linhas que são pares na ordem estabelecida por ORDER BY sejam tratadas da mesma forma: todas as linhas de um determinado grupo de pares estarão no quadro, ou serão excluídas dele.

A finalidade de uma cláusula WINDOW é especificar o comportamento das funções de janela que aparecem na lista da consulta SELECT, ou na cláusula ORDER BY. Estas funções podem fazer referência às entradas da cláusula WINDOW pelo nome em suas cláusulas OVER. Entretanto, uma entrada da cláusula WINDOW não precisa ser referenciada em nenhum lugar; se não for usada na consulta, é simplesmente ignorada. É possível usar funções de janela sem nenhuma cláusula WINDOW, já que uma chamada de função de janela pode especificar sua definição de janela diretamente em sua cláusula OVER. No entanto, a cláusula WINDOW economiza digitação quando a mesma definição de janela é necessária para mais de uma função de janela.

No momento, não pode ser especificado FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE e FOR KEY SHARE com a cláusula WINDOW.

As funções de janela são descritas em detalhes em Funções de janela, Chamadas de função de janela e Processamento de função de janela.

Lista SELECT

A lista do SELECT (entre as palavras-chave SELECT e FROM) especifica expressões que formam as linhas de saída do comando SELECT. As expressões podem (e normalmente o fazem) se referir a colunas computadas na cláusula FROM.

Assim como em uma tabela, cada coluna de saída de um comando SELECT tem um nome. Em um comando SELECT simples, este nome é usado apenas para rotular a coluna para exibição, mas quando o SELECT é uma subconsulta de uma consulta maior, o nome é visto pela consulta maior como o nome da coluna da tabela virtual produzida pela subconsulta. Para especificar o nome a ser usado para uma coluna de saída, deve ser escrito AS nome_de_saída após a expressão da coluna. (Pode ser omitido o AS, mas apenas se o nome de saída desejado não corresponder a nenhuma palavra-chave do PostgreSQL (veja Apêndice C). Para proteção contra possíveis adições futuras de palavras-chave, é recomendável que sempre se escreva o AS, ou que se coloque o nome de saída entre aspas.) Se não for especificado o nome da coluna, será escolhido um nome automaticamente pelo PostgreSQL. Se a expressão da coluna for uma simples referência de coluna, o nome escolhido será o mesmo que o nome dessa coluna. Em casos mais complexos, pode ser usado um nome de função ou de tipo de dados, ou o sistema pode recorrer a um nome gerado, como ?column?.

O nome da coluna de saída pode ser usado para se referir ao valor da coluna nas cláusulas ORDER BY e GROUP BY, mas não nas cláusulas WHERE ou HAVING; nessas cláusulas deve ser escrita a expressão em seu lugar.

Em vez de uma expressão, pode ser escrito * na lista de saída como um atalho para todas as colunas das linhas selecionadas. Além disso, também pode ser escrito nome_da_tabela.* como um atalho para as colunas provenientes apenas dessa tabela. Nesses casos não é possível especificar novos nomes usando o AS; os nomes das colunas de saída serão iguais aos nomes das colunas da tabela.

Segundo o padrão SQL, as expressões na lista de saída devem ser calculadas antes de aplicar DISTINCT, ORDER BY, ou LIMIT. Isto é obviamente necessário ao usar DISTINCT, caso contrário não fica claro quais valores estão sendo diferenciados. No entanto, em muitos casos, é conveniente que as expressões de saída sejam calculadas após ORDER BY e LIMIT; particularmente se a lista de saída contiver quaisquer funções voláteis ou dispendiosas. Com este comportamento, a ordem das avaliações das funções fica mais intuitiva, e não haverá avaliações correspondentes a linhas que nunca aparecem na saída. O PostgreSQL avaliará efetivamente as expressões de saída após a classificação e aplicação de limites, desde que estas expressões não sejam referenciadas em DISTINCT, ORDER BY ou GROUP BY. (Como contra-exemplo, SELECT f(x) FROM tab ORDER BY 1 claramente deve avaliar f(x) antes de classificar.) As expressões de saída que contêm funções que retornam conjunto são efetivamente avaliadas após a classificação e antes da aplicação dos limites, de modo que LIMIT atuará para cortar a saída de uma função que retorna conjunto.

Cláusula DISTINCT

Se for especificado SELECT DISTINCT, todas as linhas duplicadas serão removidas do conjunto de resultados (é mantida uma linha de cada grupo de duplicadas). SELECT ALL especifica o oposto: todas as linhas são mantidas; este é o padrão.

SELECT DISTINCT ON ( expressão [, ...] ) mantém apenas a primeira linha de cada conjunto de linhas onde as expressões fornecidas são avaliadas como iguais. As expressões DISTINCT ON são interpretadas usando as mesmas regras usadas para ORDER BY (veja acima). Note que a “primeira linha” de cada conjunto é imprevisível, a menos que seja usado ORDER BY para garantir que a linha desejada apareça primeiro. Por exemplo:

SELECT DISTINCT ON (local) local, hora, relatório
    FROM boletim_meteorológico
    ORDER BY local, hora DESC;

recupera o boletim meteorológico mais recente para cada local. Mas se não tivesse sido usado ORDER BY para forçar a ordem decrescente dos valores da hora para cada local, teria sido obtido um relatório com uma hora imprevisível para cada local.

As expressões DISTINCT ON devem corresponder às expressões ORDER BY mais à esquerda. A cláusula ORDER BY normalmente contém expressões adicionais que determinam a precedência desejada das linhas dentro de cada grupo DISTINCT ON.

No momento, não pode ser especificado FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE e FOR KEY SHARE com DISTINCT.

Cláusula UNION

A cláusula UNION tem a forma geral

comando_select UNION [ ALL | DISTINCT ] comando_select

onde comando_select é qualquer comando SELECT sem uma cláusula ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, ou FOR KEY SHARE. (ORDER BY e LIMIT podem ser anexados a uma subexpressão se estiverem entre parênteses. Sem parênteses, estas cláusulas serão aplicadas ao resultado da cláusula UNION, e não à sua expressão de entrada à direita.)

O operador UNION computa a união dos conjuntos de linhas retornados pelos comandos SELECT envolvidos. Uma linha está na união de dois conjuntos de resultados se aparecer em pelo menos um dos conjuntos de resultados. Os dois comandos SELECT que representam os operandos diretos da cláusula UNION devem produzir o mesmo número de colunas, e as colunas correspondentes devem ser de tipos de dados compatíveis.

O resultado da cláusula UNION não contém nenhuma linha duplicada, a menos que seja especificada a opção ALL, que impede a eliminação de linhas duplicadas. (Portanto, UNION ALL é geralmente muito mais rápida do que UNION, devendo ser usada quando for possível.) Pode ser escrito DISTINCT para especificar explicitamente o comportamento padrão de eliminação das linhas duplicadas.

Múltiplas cláusulas UNION no mesmo comando SELECT são avaliadas da esquerda para a direita, a menos que seja indicado de outra forma usando parênteses.

No momento, não pode ser especificado FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE e FOR KEY SHARE, seja para o resultado ou para qualquer entrada da cláusula UNION.

Cláusula INTERSECT

A cláusula INTERSECT tem a forma geral

comando_select INTERSECT [ ALL | DISTINCT ] comando_select

onde comando_select é qualquer comando SELECT sem uma cláusula ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, ou FOR KEY SHARE.

O operador INTERSECT computa a interseção dos conjuntos de linhas retornados pelos comandos SELECT envolvidos. Uma linha está na interseção dos dois conjuntos de resultados se aparecer nos dois conjuntos de resultados.

O resultado da cláusula INTERSECT não contém nenhuma linha duplicada, a menos que seja especificada a opção ALL. Com a opção ALL, uma linha que tem m duplicações na tabela à esquerda, e n duplicações na tabela à direita, aparecerá min(m, n) vezes no conjunto de resultados. Pode ser escrito DISTINCT para especificar explicitamente o comportamento padrão de eliminação das linhas duplicadas.

Múltiplas cláusulas INTERSECT no mesmo comando SELECT são avaliadas da esquerda para a direita, a menos que seja indicado de outra forma usando parênteses. A cláusula INTERSECT liga mais firmemente do que a cláusula UNION. Ou seja, A UNION B INTERSECT C será interpretada como A UNION (B INTERSECT C).

No momento, não pode ser especificado FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE e FOR KEY SHARE, seja para o resultado ou para qualquer entrada da cláusula INTERSECT.

Cláusula EXCEPT

A cláusula EXCEPT tem a forma geral

comando_select EXCEPT [ ALL | DISTINCT ] comando_select

onde comando_select é qualquer comando SELECT sem uma cláusula ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, ou FOR KEY SHARE.

O operador EXCEPT computa o conjunto de linhas que estão no resultado do comando SELECT da esquerda, mas não estão no resultado do comando da direita.

O resultado de EXCEPT não contém nenhuma linha duplicada, a menos que a opção ALL seja especificada. Com a opção ALL, uma linha que tem m duplicações na tabela à esquerda, e n duplicações na tabela à direita, aparecerá max(m-n, 0) vezes no conjunto de resultados. Pode ser escrito DISTINCT para especificar explicitamente o comportamento padrão de eliminação das linhas duplicadas.

Múltiplas cláusulas EXCEPT no mesmo comando SELECT são avaliadas da esquerda para a direita, a menos que seja indicado de outra forma usando parênteses. A cláusula EXCEPT liga no mesmo nível que a cláusula UNION.

No momento, não pode ser especificado FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE e FOR KEY SHARE, seja para o resultado ou para qualquer entrada da cláusula EXCEPT.

Cláusula ORDER BY

A cláusula opcional ORDER BY tem a forma geral

ORDER BY expressão [ ASC | DESC | USING operador ] [ NULLS { FIRST | LAST } ] [, ...]

A cláusula ORDER BY faz com que as linhas do resultado sejam classificadas conforme a(s) expressão(ões) especificada(s). Se duas linhas forem iguais segundo a expressão mais à esquerda, elas serão comparadas segundo a expressão seguinte, e assim por diante. Se forem iguais de acordo com todas as expressões especificadas, serão retornadas em uma ordem dependente da implementação.

Cada expressão pode ser o nome ou número ordinal de uma coluna de saída (item da lista SELECT), ou pode ser uma expressão arbitrária formada a partir de valores de coluna de entrada.

O número ordinal refere-se à posição ordinal (da esquerda para a direita) da coluna de saída. Este recurso permite definir uma ordenação com base em uma coluna que não possui um nome único. Isto nunca é inteiramente necessário, porque sempre é possível atribuir um nome a uma coluna de saída usando a cláusula AS.

Também é possível usar expressões arbitrárias na cláusula ORDER BY, incluindo colunas que não aparecem na lista de saída do comando SELECT. Assim, o seguinte comando é válido:

SELECT name FROM distributors ORDER BY code;

Uma limitação desse recurso é que uma cláusula ORDER BY aplicada ao resultado de uma cláusula UNION, INTERSECT, ou EXCEPT, só pode especificar um nome ou número de coluna de saída, e não uma expressão.

Se a expressão da cláusula ORDER BY for um nome simples, que corresponda a um nome de coluna de saída e a um nome de coluna de entrada, a cláusula ORDER BY irá interpretá-lo como o nome da coluna de saída. Esta escolha é o oposto da que a cláusula GROUP BY faz na mesma situação. Esta inconsistência é feita para ficar compatível com o padrão SQL.

Como opção, pode-se adicionar a palavra-chave ASC (ascendente) ou DESC (descendente) após qualquer expressão na cláusula ORDER BY. Se não especificado, é assumido ASC por padrão. Como alternativa, pode ser especificado um nome de operador de ordenação específico na cláusula USING. O operador de ordenação deve ser um membro “menor que” ou “maior que” de alguma família de operadores de Árvore-B. ASC é geralmente equivalente a USING <, e DESC é geralmente equivalente a USING >. (Mas o criador de um tipo de dados definido pelo usuário pode definir exatamente qual é a ordem de classificação padrão, e pode corresponder a operadores com outros nomes.)

Se for especificado NULLS LAST, os valores nulos serão classificados após todos os valores não nulos; se for especificado NULLS FIRST, os valores nulos serão classificados antes de todos os valores não nulos. Se não for especificado nenhum dos dois, o comportamento padrão será NULLS LAST quando ASC for especificado ou for implícito, e NULLS FIRST quando DESC for especificado (portanto, o padrão é agir como se os nulos fossem maiores que os não nulos). Quando é especificado USING, a ordem padrão dos nulos depende se o operador é “menor que”, ou “maior que”.

Note que as opções de ordenação se aplicam apenas à expressão que elas seguem; por exemplo, ORDER BY x, y DESC não significa a mesma coisa que ORDER BY x DESC, y DESC.

Os dados de cadeia de caracteres são classificados segundo a ordenação que se aplica à coluna que está sendo classificada. Isto pode ser mudado se necessário, incluindo uma cláusula COLLATE na expressão, por exemplo, ORDER BY minha_coluna COLLATE "pt_BR". Para mais obter informações, veja Expressões de ordenação e Suporte a ordenação.

Cláusula LIMIT

A cláusula LIMIT consiste em duas subcláusulas independentes:

LIMIT { contador | ALL }
OFFSET início

O parâmetro contador especifica o número máximo de linhas a serem retornadas, enquanto início especifica o número de linhas a serem ignoradas antes de começar a retornar linhas. Quando os dois são especificados, início linhas são saltadas antes de começar a contar o contador linhas a serem retornadas.

Se a expressão contador for avaliada como NULL, ela será tratada como LIMIT ALL, ou seja, sem limite. Se início for avaliado como NULL, ele será tratada como OFFSET 0.

O padrão SQL:2008 introduziu uma sintaxe diferente para obter o mesmo resultado, que o PostgreSQL também dá suporte. Ela é:

OFFSET início { ROW | ROWS }
FETCH { FIRST | NEXT } [ contador ] { ROW | ROWS } { ONLY | WITH TIES }

Nesta sintaxe, o valor de início ou do contador é exigido pelo padrão como uma constante literal, um parâmetro, ou um nome de variável; como extensão do PostgreSQL, são permitidas outras expressões, mas geralmente precisam ser colocadas entre parênteses para evitar ambiguidade. Se for omitido o contador em uma cláusula FETCH, o padrão será 1. A opção WITH TIES é usada para retornar quaisquer linhas adicionais que se igualam na última posição no conjunto de resultados segundo a cláusula ORDER BY; neste caso ORDER BY é obrigatório, enquanto SKIP LOCKED não é permitido. ROW e ROWS, assim como FIRST e NEXT, são palavras ruído que não influenciam os efeitos dessas cláusulas. Segundo o padrão, a cláusula OFFSET deve vir antes da cláusula FETCH, se as duas estiverem presentes; mas o PostgreSQL é mais flexível e permite qualquer ordem.

Ao usar LIMIT, é uma boa ideia usar a cláusula ORDER BY que restringe as linhas de resultado em uma ordem única. Caso contrário, será obtido um subconjunto imprevisível das linhas da consulta — pode-se estar querendo da décima à vigésima linha, mas da décima à vigésima linha em que ordem? Não se sabe em que ordem, a menos que seja especificado ORDER BY.

O planejador de consulta leva LIMIT em consideração ao gerar um plano para a consulta, então é muito provável que se obtenha planos diferentes (resultando em diferentes ordens de linha) dependendo do que é usado para LIMIT e OFFSET. Assim, utilizar valores diferentes de LIMIT e OFFSET para selecionar subconjuntos diferentes de um resultado da consulta dará resultados inconsistentes, a menos que se imponha uma ordem previsível de resultados com ORDER BY. Isto não é um bug; isto é uma consequência inerente do fato de que o padrão SQL não promete entregar os resultados de uma consulta em qualquer ordem específica, a menos que seja usado ORDER BY para impor a ordem.

É possível que até execuções repetidas da mesma consulta com a cláusula LIMIT retornem diferentes subconjuntos de linhas de uma tabela, se não houver ORDER BY para impor a seleção de um subconjunto determinístico. Novamente, isto não é um bug; o determinismo dos resultados simplesmente não é garantido neste caso.

Cláusula de bloqueio

As cláusulas FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE e FOR KEY SHARE são cláusulas de bloqueio; afetam como o comando SELECT bloqueia as linhas conforme elas são obtidas da tabela.

A cláusula de bloqueio tem a forma geral

FOR força_de_bloqueio [ OF referência_FROM [, ...] ] [ NOWAIT | SKIP LOCKED ]

onde a força_de_bloqueio pode ser uma entre

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE

referência_FROM deve ser um alias de tabela ou um nome_da_tabela não oculto referenciado na cláusula FROM. Para obter mais informações sobre cada modo de bloqueio no nível de linha, veja Modos de bloqueio no nível de linha.

Para evitar que a operação aguarde por outras transações, deve ser usada a opção NOWAIT ou SKIP LOCKED. Com NOWAIT, se uma linha selecionada não puder ser bloqueada imediatamente, a consulta relata um erro, em vez de aguardar. Com SKIP LOCKED, são ignoradas quaisquer linhas selecionadas que não puderem ser bloqueadas imediatamente. Ignorar as linhas bloqueadas fornece uma visão inconsistente dos dados; portanto, não é adequado para o trabalho de uso geral, mas pode ser usado para evitar a contenção de bloqueio com vários consumidores acessando uma tabela semelhante à fila. Note que NOWAIT e SKIP LOCKED se aplicam apenas ao(s) bloqueio(s) no nível de linha — o bloqueio ROW SHARE no nível de tabela ainda é realizado da maneira comum (veja o Controle de concorrência). Pode ser usado primeiro LOCK com a opção NOWAIT, se for necessário adquirir o bloqueio no nível de tabela sem aguardar.

Se forem indicadas tabelas específicas em uma cláusula de bloqueio, apenas as linhas provenientes dessas tabelas serão bloqueadas; quaisquer outras tabelas presentes no comando SELECT serão simplesmente lidas como de costume. Uma cláusula de bloqueio sem uma lista de tabelas afeta todas as tabelas usadas no comando. Se uma cláusula de bloqueio for aplicada a uma visão ou subconsulta, ela afetará todas as tabelas usadas na visão ou na subconsulta. Entretanto, estas cláusulas não se aplicam a consultas WITH referenciadas pela consulta primária. Se for desejado que ocorra bloqueio de linha numa consulta WITH, deve ser especificada uma cláusula de bloqueio dentro da WITH.

Podem ser escritas várias cláusulas de bloqueio, se for necessário especificar comportamentos de bloqueio diferentes para tabelas específicas. Se a mesma tabela for mencionada (ou implicitamente afetada) por mais de uma cláusula de bloqueio, ela será processada como se fosse atingida apenas pela cláusula mais forte. De forma semelhante, uma tabela será processada como NOWAIT se isto for especificado em alguma das cláusulas que a afetam. Caso contrário, será processada como SKIP LOCKED se isto for especificado em alguma das cláusulas que a afetam.

As cláusulas de bloqueio não podem ser usadas em contextos onde as linhas retornadas não podem ser claramente identificadas com linhas individuais de tabela; por exemplo, não podem ser usadas com agregações.

Quando uma cláusula de bloqueio aparece no nível mais alto de um comando SELECT, as linhas bloqueadas são exatamente aquelas retornadas pela consulta; no caso de uma consulta de junção, as linhas bloqueadas são aquelas que contribuem para as linhas de junção retornadas. Além disso, as linhas que atendiam às condições da consulta no instantâneo da consulta serão bloqueadas, embora não sejam retornadas se forem atualizadas após o instantâneo e não satisfizerem mais as condições da consulta. Se for usada uma cláusula LIMIT, o bloqueio é encerrado logo após serem retornadas linhas suficientes para satisfazer o limite (mas note-se que as linhas saltadas devido ao OFFSET são bloqueadas). De forma semelhante, se for usada uma cláusula de bloqueio na consulta de um cursor, apenas as linhas realmente recuperadas ou percorridas pelo cursor serão bloqueadas.

Quando uma cláusula de bloqueio aparece em um sub-SELECT, as linhas bloqueadas são as retornadas à consulta externa pela subconsulta. Isto pode envolver menos linhas do que a inspeção da subconsulta sugere, uma vez que as condições da consulta externa podem ser usadas para otimizar a execução da subconsulta. Por exemplo,

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

bloqueia apenas as linhas com col1 = 5, muito embora esta condição não esteja textualmente dentro da subconsulta.

As versões anteriores falhavam em preservar um bloqueio atualizado por um ponto de salvamento posterior. Por exemplo, o código

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

deixaria de preservar o bloqueio FOR UPDATE após o ROLLBACK TO. Isto foi corrigido na versão 9.3.

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

Comando TABLE

O comando

TABLE nome_da_tabela

é equivalente a

SELECT * FROM nome_da_tabela

Pode ser usado como um comando de nível superior, ou como uma variante de sintaxe que economiza espaço em partes de consultas complexas. Somente WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH, e as cláusulas de bloqueio FOR podem ser usadas com TABLE; a cláusula WHERE, e qualquer forma de agregação, não podem ser usadas com TABLE.

Cláusulas FROM omitidas

O PostgreSQL permite omitir a cláusula FROM, tendo uso direto para calcular os resultados de expressões simples:

SELECT 2+2;

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

Alguns outros bancos de dados SQL não permitem fazer isto, exceto introduzindo de uma tabela fictícia de uma linha a partir da qual fazer o SELECT.

Listas SELECT vazias

A lista de expressões de saída após o SELECT pode estar vazia, produzindo uma tabela de resultados zero-colunas. Esta não é uma sintaxe válida segundo o padrão SQL. O PostgreSQL permite isto para ser consistente com a permissão de tabelas zero-colunas. Entretanto, não é permitida uma lista vazia quando é usado DISTINCT.

Omissão da palavra-chave AS

No padrão SQL, a palavra-chave opcional AS antes de um nome de coluna de saída pode ser omitida sempre que o novo nome de coluna for um nome de coluna válido (ou seja, não for idêntico a qualquer palavra-chave reservada). PostgreSQL é um pouco mais restritivo: AS é necessário se o nome da nova coluna corresponder a qualquer palavra-chave, reservada ou não. A prática recomendada é usar AS, ou nomes de colunas de saída entre aspas, para evitar qualquer possível conflito contra futuras adições de palavras-chave.

Nos itens da cláusula FROM, tanto o padrão SQL quanto o PostgreSQL permitem que seja omitido o AS antes de um alias que seja uma palavra-chave não reservada. Mas isto é impraticável para nomes de coluna de saída, devido a ambiguidades sintáticas.

Omissão de aliases do sub-SELECT no FROM

Segundo o padrão SQL, um subcomando SELECT na lista FROM deve ter um alias. No PostgreSQL este alias pode ser omitido.

ONLY e herança

O padrão SQL requer o uso de parênteses ao redor do nome da tabela ao escrever ONLY, por exemplo, SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... O PostgreSQL considera estes parênteses opcionais.

O PostgreSQL permite ser escrito um * à direita para especificar explicitamente o comportamento não-ONLY de incluir as tabelas filhas. O padrão SQL não permite isto.

(Estes pontos se aplicam igualmente a todos os comandos SQL que dão suporte a opção ONLY.)

Restrições da cláusula TABLESAMPLE

A cláusula TABLESAMPLE é atualmente aceita apenas em tabelas regulares e visões materializadas. Segundo o padrão SQL, deve ser possível aplicá-la a qualquer item da cláusula FROM.

Chamadas de função no FROM

O PostgreSQL permite ser escrita uma chamada de função diretamente como um membro da lista da cláusula FROM. Pelo padrão SQL seria necessário agrupar a chamada de função em um sub-SELECT; ou seja, a sintaxe FROM func(...) alias é aproximadamente equivalente a FROM LATERAL (SELECT func(...)) alias. Note que LATERAL é considerado implícito; isto acontece porque o padrão SQL requer a semântica LATERAL para um item UNNEST() na cláusula FROM. O PostgreSQL trata UNNEST() da mesma forma que outras funções que retornam conjunto.

Espaço de nomes disponível para GROUP BY e ORDER BY

No padrão SQL-92, uma cláusula ORDER BY só pode usar nomes ou números de colunas de saída, enquanto uma cláusula GROUP BY pode usar apenas expressões baseadas em nomes de colunas de entrada. O PostgreSQL estende cada uma dessas cláusulas para permitir a outra escolha também (mas usa a interpretação do padrão se houver ambiguidade). O PostgreSQL também permite que as duas cláusulas especifiquem expressões arbitrárias. Note que os nomes que aparecem em uma expressão sempre serão considerados sendo nomes de colunas de entrada, e não nomes de colunas de saída.

O padrão SQL:1999, e posteriores, usam uma definição ligeiramente diferente que não é totalmente compatível com o SQL-92. No entanto, geralmente o PostgreSQL interpreta uma expressão ORDER BY ou GROUP BY da mesma forma que o SQL:1999.

Dependências funcionais

O PostgreSQL reconhece dependência funcional (permitindo que sejam omitidas colunas de GROUP BY) somente quando a chave primária de uma tabela está incluída na lista da cláusula GROUP BY. O padrão SQL especifica condições adicionais que devem ser reconhecidas.

LIMIT e OFFSET

As cláusulas LIMIT e OFFSET são sintaxes específicas do PostgreSQL, também usadas pelo MySQL. O padrão SQL:2008 introduziu as cláusulas OFFSET ... FETCH {FIRST|NEXT} ... para a mesma funcionalidade, conforme mostrado acima na Cláusula LIMIT. Esta sintaxe também é usada pelo IBM DB2. (Aplicações escritas para Oracle frequentemente usam uma solução envolvendo a coluna rownum gerada automaticamente, que não está disponível no PostgreSQL, para implementar os efeitos dessas cláusulas.)

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

Embora FOR UPDATE conste no padrão SQL, o padrão permite apenas como opção de DECLARE CURSOR. O PostgreSQL o permite em qualquer consulta SELECT, bem como em sub-SELECTs, mas isto é uma extensão. As variantes FOR NO KEY UPDATE, FOR SHARE e FOR KEY SHARE, assim como as opções NOWAIT e SKIP LOCKED, não constam no padrão.

Declarações de modificação de dados no WITH

O PostgreSQL permite que INSERT, UPDATE, e DELETE sejam usados nas consultas WITH. Isto não consta no padrão SQL.

Cláusulas fora do padrão

DISTINCT ON ( ... ) é uma extensão ao padrão SQL.

ROWS FROM( ... ) é uma extensão ao padrão SQL.

As opções MATERIALIZED e NOT MATERIALIZED da cláusula WITH são extensões do padrão SQL.