Предложение WITH

Предложение WITH позволяет задать один или несколько подзапросов, к которым затем можно обратиться по имени в основном запросе. Эти подзапросы по сути действуют как временные таблицы или представления в процессе выполнения главного запроса. Каждый подзапрос может представлять собой оператор SELECT, TABLE, VALUES, INSERT, UPDATE или DELETE. При использовании в WITH оператора, изменяющего данные, (INSERT, UPDATE или DELETE) обычно добавляется предложение RETURNING. Заметьте, что именно результат RETURNING, а не нижележащая таблица, изменяемая запросом, формирует временную таблицу, которую затем читает основной запрос. Если RETURNING опущено, оператор тем не менее выполняется, но не выдаёт никакого результата, так что на него нельзя сослаться как на таблицу в основном запросе.

Имя (без схемы) должно быть указано для каждого запроса WITH. Также можно задать необязательный список с именами столбцов; если он опущен, имена столбцов формируются из результата подзапроса.

Если указано RECURSIVE, подзапрос SELECT может ссылаться сам на себя по имени. Такой подзапрос должен иметь форму

нерекурсивная_часть UNION [ ALL | DISTINCT ] рекурсивная_часть

, где рекурсивная ссылка на сам запрос может находиться только справа от UNION. Для одного запроса допускается только одна рекурсивная ссылка на него же. Операторы, изменяющие данные, не могут быть рекурсивными, но результат рекурсивного запроса SELECT в таких операторах можно использовать. За примером обратитесь к Разделу 7.8.

Ещё одна особенность RECURSIVE в том, что запросы WITH могут быть неупорядоченными: запрос может ссылаться на другой, идущий в списке после него. (Однако циклические ссылки или взаимная рекурсия не поддерживаются.) Без RECURSIVE запрос в WITH может ссылаться только на запросы того же уровня в WITH, предшествующие ему в списке WITH.

Когда в предложении WITH задаются несколько запросов, RECURSIVE следует указывать только единожды, сразу после WITH. Это указание будет действовать на все запросы в предложении WITH, хотя оно никак не скажется на запросах, не использующих рекурсию или ссылки на последующие запросы.

Необязательное предложение SEARCH вычисляет столбец последовательности поиска, который может применяться для упорядочивания результатов рекурсивного запроса «сначала в ширину» или «сначала в глубину». Передаваемый список имён столбцов задаёт ключ строки, по которому будет отслеживаться, какие строки были посещены. Столбец с именем имя_столбца_послед_поиска будет добавлен в список результирующих столбцов запроса WITH. Для получения нужного порядка можно выполнить сортировку по этому столбцу во внешнем запросе. За примерами обратитесь к Подразделу 7.8.2.1.

Необязательное предложение CYCLE применяется для обнаружения циклов в рекурсивных запросах. Задаваемый в нём список имён столбцов определяет ключ строки, по которому будут отслеживаться посещённые строки. Столбец имя_столбца_пометки_цикла будет добавлен в список результирующих столбцов запроса WITH. В случае обнаружения цикла в этом столбце окажется значение_пометки_цикла, а иначе — пометка_цикла_по_умолчанию. Более того, в случае обнаружения цикла обработка рекурсивного объединения будет остановлена. Задаваемые значение_пометки_цикла и пометка_цикла_по_умолчанию должны быть константами и должны приводиться к общему типу данных, а для этого типа должен быть определён оператор неравенства. (Стандарт SQL требует, чтобы они были булевыми константами или символьными строками, но в PostgreSQL это необязательно.) По умолчанию подразумеваются соответственно константы TRUE и FALSE (типа boolean). Кроме того, в список результирующих столбцов запроса WITH будет добавлен столбец имя_столбца_пути_цикла. Этот столбец имеет внутреннее предназначение, в нём отслеживаются посещённые строки. За примерами обратитесь к Подразделу 7.8.2.2.

Предложения SEARCH и CYCLE работают только с рекурсивными запросами WITH. При этом запрос_WITH должен быть объединением (UNION или UNION ALL) двух SELECT (или равнозначных команд) без вложенных UNION. В случае применения обоих предложений столбец, добавляемый предложением SEARCH, размещается перед столбцами, добавляемыми предложением CYCLE.

Основной запрос и все запросы WITH, условно говоря, выполняются одновременно. Это значит, что действие оператора, изменяющего данные в WITH, не будут видеть другие части запроса, кроме как прочитав его вывод RETURNING. Если два таких оператора попытаются изменить одну строку, результат будет неопределённым.

Ключевое свойство запросов WITH состоит в том, что они обычно вычисляются один раз для всего основного запроса, даже если в основном запросе содержатся несколько ссылок на них. В частности, гарантируется, что операторы, изменяющие данные, будут выполняться ровно один раз, вне зависимости от того, будет ли их результат прочитан основным запросом и в каком объёме.

Однако от этой гарантии можно отказаться, добавив для запроса WITH пометку NOT MATERIALIZED. В этом случае запрос WITH может быть свёрнут в основной запрос, как если бы это был простой SELECT внутри предложения FROM основного запроса. В результате запрос WITH может вычисляться неоднократно, если основной запрос обращается к нему несколько раз. Но если при каждом таком обращении требуются лишь отдельные строки из всего результата запроса WITH, указание NOT MATERIALIZED, позволяющее оптимизировать запросы совместно, в целом может оказаться выгодным. Указание NOT MATERIALIZED игнорируется в запросе WITH, который имеет рекурсивный характер или не свободен от побочных эффектов (то есть когда это не простой SELECT без изменчивых функций).

По умолчанию запрос WITH, свободный от побочных эффектов, заворачивается в основной запрос, если он используется в его предложении FROM ровно один раз. Это позволяет совместно оптимизировать два уровня запросов в ситуациях, когда семантика запроса в целом сохраняется. Но это заворачивание можно предотвратить, пометив запрос WITH как MATERIALIZED. Это может быть полезно, например когда предложение WITH применяется как преграда для оптимизатора, не позволяющая ему выбрать неудачный план. В PostgreSQL до 12 версии такое заворачивание не выполнялось никогда, поэтому запросы, написанные для предыдущих версий, могли полагаться на то, что оптимизации будет препятствовать собственно предложение WITH.

За дополнительными сведениями обратитесь к Разделу 7.8.

Предложение FROM

В предложении FROM перечисляются одна или несколько таблиц, служащих источниками данных для SELECT. Если указано несколько источников, результатом будет декартово произведение (перекрёстное соединение) всех их строк. Но обычно в запрос добавляются уточняющие условия (в предложении WHERE), которые ограничивают набор строк небольшим подмножеством этого произведения.

Предложение FROM может содержать следующие элементы:

Предложение WHERE

Необязательное предложение WHERE имеет общую форму

WHERE условие

, где условие — любое выражение, выдающее результат типа boolean. Любая строка, не удовлетворяющая этому условию, исключается из результата. Строка удовлетворяет условию, если оно возвращает true при подстановке вместо ссылок на переменные фактических значений из этой строки.

Предложение GROUP BY

Необязательное предложение GROUP BY имеет общую форму

GROUP BY [ ALL | DISTINCT ] элемент_группирования [, ...]

GROUP BY собирает в одну строку все выбранные строки, выдающие одинаковые значения для выражений группировки. В качестве выражения внутри элемента_группирования может выступать имя входного столбца, либо имя или порядковый номер выходного столбца (из списка элементов SELECT), либо произвольное значение, вычисляемое по значениям входных столбцов. В случае неоднозначности имя в GROUP BY будет восприниматься как имя входного, а не выходного столбца.

Если в элементе группирования задаётся GROUPING SETS, ROLLUP или CUBE, предложение GROUP BY в целом определяет некоторое число независимых наборов группирования. Это даёт тот же эффект, что и объединение подзапросов (с UNION ALL) с отдельными наборами группирования в их предложениях GROUP BY. Необязательное предложение DISTINCT исключает повторяющиеся наборы перед обработкой, но оно не преобразует UNION ALL в UNION DISTINCT. Подробнее использование наборов группирования описывается в Подразделе 7.2.4.

Агрегатные функции, если они используются, вычисляются по всем строкам, составляющим каждую группу, и в итоге выдают отдельное значение для каждой группы. (Если агрегатные функции используются без предложения GROUP BY, запрос выполняется как с одной группой, включающей все выбранные строки.) Набор строк, поступающих в каждую агрегатную функцию, можно дополнительно отфильтровать, добавив предложение FILTER к вызову агрегатной функции; за дополнительными сведениями обратитесь к Подразделу 4.2.7. С предложением FILTER на вход агрегатной функции поступают только те строки, которые соответствуют заданному фильтру.

Когда в запросе присутствует предложение GROUP BY или какая-либо агрегатная функция, выражения в списке SELECT не могут обращаться к негруппируемым столбцам, кроме как в агрегатных функциях или в случае функциональной зависимости, так как иначе в негруппируемом столбце нужно было бы вернуть более одного возможного значения. Функциональная зависимость образуется, если группируемые столбцы (или их подмножество) составляют первичный ключ таблицы, содержащей негруппируемый столбец.

Имейте в виду, что все агрегатные функции вычисляются перед «скалярными» выражениями в предложении HAVING или списке SELECT. Это значит, что например, с помощью выражения CASE нельзя обойти вычисление агрегатной функции; см. Подраздел 4.2.14.

В настоящее время указания FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя задать вместе с GROUP BY.

Предложение HAVING

Необязательное предложение HAVING имеет общую форму

HAVING условие

Здесь условие задаётся так же, как и для предложения WHERE.

HAVING исключает из результата строки групп, не удовлетворяющих условию. HAVING отличается от WHERE: WHERE фильтрует отдельные строки до применения GROUP BY, а HAVING фильтрует строки групп, созданных предложением GROUP BY. Каждый столбец, фигурирующий в условии, должен однозначно ссылаться на группируемый столбец, за исключением случаев, когда эта ссылка находится внутри агрегатной функции или негруппируемый столбец функционально зависит от группируемых.

В присутствие HAVING запрос превращается в группируемый, даже если GROUP BY отсутствует. То же самое происходит, когда запрос содержит агрегатные функции, но не предложение GROUP BY. Все выбранные строки считаются формирующими одну группу, а в списке SELECT и предложении HAVING можно обращаться к столбцам таблицы только из агрегатных функций. Такой запрос будет выдавать единственную строку, если результат условия HAVING — true, и ноль строк в противном случае.

В настоящее время указания FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя задать вместе с HAVING.

Предложение WINDOW

Необязательное предложение WINDOW имеет общую форму

WINDOW имя_окна AS ( определение_окна ) [, ...]

Здесь имя_окна — это имя, на которое можно ссылаться из предложений OVER или последующих определений окон, а определение_окна имеет следующий вид:

[ имя_существующего_окна ]
[ PARTITION BY выражение [, ...] ]
[ ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ предложение_рамки ]

Если указано имя_существующего_окна, оно должно ссылаться на предшествующую запись в списке WINDOW; новое окно копирует предложение секционирования из этой записи, а также предложение сортировки, если оно присутствует. В этом случае для нового окна нельзя задать собственное предложение PARTITION BY, а ORDER BY можно указать, только если его не было у копируемого окна. Новое окно всегда использует собственное предложение рамки; в копируемом окне оно задаваться не должно.

Элементы списка PARTITION BY интерпретируется во многом так же, как и элементы списка GROUP BY, за исключением того, что это всегда простые выражения, но не имя или номер выходного столбца. Другое различие состоит в том, что эти выражения могут содержать вызовы агрегатных функций, которые не допускаются в обычном предложении GROUP BY. Здесь они допускаются потому, что формирование окна происходит после группировки и агрегирования.

Подобным образом, элементы списка ORDER BY интерпретируются во многом так же, как и элементы предложения ORDER BY на уровне оператора, за исключением того, что они всегда воспринимаются как простые выражения, но не как имя или номер выходного столбца.

Необязательное предложение_рамки определяет рамку окна для оконных функций, которые зависят от рамки (не все функции таковы). Рамка окна — это набор связанных строк для каждой строки запроса (называемой текущей строкой). В качестве предложения_рамки может задаваться

{ RANGE | ROWS | GROUPS } начало_рамки [ исключение_рамки ]
{ RANGE | ROWS | GROUPS } BETWEEN начало_рамки AND конец_рамки [ исключение_рамки ]

Здесь начало_рамки и конец_рамки может задаваться как

UNBOUNDED PRECEDING
смещение PRECEDING
CURRENT ROW
смещение FOLLOWING
UNBOUNDED FOLLOWING

и исключение_рамки может быть таким:

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

Если конец_рамки опущен, по умолчанию подразумевается CURRENT ROW. В качестве начала_рамки нельзя задать UNBOUNDED FOLLOWING, в качестве конца_рамки не допускается UNBOUNDED PRECEDING, и конец_рамки не может идти в показанном выше списке указаний начало_рамки AND конец_рамки перед началом_рамки — например, синтаксис RANGE BETWEEN CURRENT ROW AND смещение PRECEDING не допускается.

По умолчанию рамка образуется предложением RANGE UNBOUNDED PRECEDING, что по сути то же, что RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; оно устанавливает рамку так, что она включает все строки от начала раздела до последней строки, родственной текущей (строки, которые согласно указанному для окна предложению ORDER BY считаются равными текущей; если ORDER BY отсутствует, все строки считаются родственными). Вообще UNBOUNDED PRECEDING означает, что рамка начинается с первой строки раздела, а UNBOUNDED FOLLOWING означает, что рамка заканчивается на последней строке раздела, независимо от режима RANGE, ROWS или GROUPS. В режиме ROWS указание CURRENT ROW означает, что рамка начинается или заканчивается текущей строкой; но в режиме RANGE или GROUPS оно означает, что рамка начинается или заканчивается первой или последней строкой, родственной текущей, согласно порядку ORDER BY. Варианты смещение PRECEDING и смещение FOLLOWING означают разное в зависимости от режима рамки. В режиме ROWS целочисленное смещение определяет сдвиг, с которым начало рамки позиционируется перед текущей строкой, а конец рамки — после текущей строки. В режиме GROUPS целочисленное смещение аналогичным образом определяет сдвиг относительно группы строк, родственных текущей, где группа родственных строк — группа строк, считающихся равными согласно предложению ORDER BY для данного окна. В режиме RANGE для указания смещения необходимо присутствие в определении окна ровно одного столбца ORDER BY. Тогда рамка будет содержать те строки, в которых значение упорядочивающего столбца не более чем на смещение меньше (для PRECEDING) или больше (для FOLLOWING) значения упорядочивающего столбца в текущей строке. В этом случае тип данных выражения смещение зависит от типа данных упорядочивающего столбца. Для числовых столбцов это обычно тот же числовой тип, а для столбцов с типом дата/время — тип interval. Во всех этих случаях значение смещения должно быть отличным от NULL и неотрицательным. Кроме того, хотя смещение не обязательно должно быть простой константой, оно не может содержать переменные, агрегатные или оконные функции.

Дополнение исключение_рамки позволяет исключить из рамки строки, которые окружают текущую строку, даже если они должны быть включены согласно указаниям, определяющим начало и конец рамки. EXCLUDE CURRENT ROW исключает из рамки текущую строку. EXCLUDE GROUP исключает из рамки текущую строку и родственные ей согласно порядку сортировки. EXCLUDE TIES исключает из рамки все родственные строки для текущей, но не собственно текущую строку. EXCLUDE NO OTHERS просто явно выражает поведение по умолчанию — не исключает ни текущую строку, ни родственные ей.

Учтите, что в режиме ROWS могут выдаваться непредсказуемые результаты, если согласно порядку, заданному в ORDER BY, строки сортируются неоднозначно. Режимы RANGE и GROUPS предусмотрены для того, чтобы строки, являющиеся родственными в порядке ORDER BY, обрабатывались одинаково: все строки определённой группы попадут в одну рамку или будут исключены из неё.

Предложение WINDOW применяется для управления поведением оконных функций, фигурирующих в запросе, в списке SELECT или предложении ORDER BY. Эти функции могут обращаться к элементам WINDOW по именам в своих предложениях OVER. При этом элементы WINDOW не обязательно задействовать в запросе; если они не используются, они просто игнорируются. Оконные функции можно использовать вовсе без элементов WINDOW, так как в вызове оконной функции можно задать определение окна непосредственно в предложении OVER. Однако предложение WINDOW позволяет сократить текст запроса, когда одно и то же определение окна применяется при вызове нескольких оконных функций.

В настоящее время указания FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя задать вместе с WINDOW.

Оконные функции подробно описываются в Разделе 3.5, Подразделе 4.2.8 и Подразделе 7.2.5.

Список SELECT

Список SELECT (между ключевыми словами SELECT и FROM) содержит выражения, которые формируют выходные строки оператора SELECT. Эти выражения могут обращаться (и обычно обращаются) к столбцам, вычисленным в предложении FROM.

Так же, как в таблице, каждый выходной столбец SELECT имеет имя. В простом предложении SELECT это имя просто помечает столбец при выводе, но когда SELECT представляет собой подзапрос большого запроса, это имя большой запрос видит как имя столбца виртуальной таблицы, созданной подзапросом. Чтобы задать имя для выходного столбца, нужно написать AS выходное_имя после выражения столбца. (Слово AS можно опустить, но только если желаемое выходное имя не совпадает с каким-либо ключевым словом PostgreSQL (см. Приложение C). Чтобы не зависеть от появления новых ключевых слов в будущем, рекомендуется всегда писать AS, либо заключать имя в двойные кавычки.) Если имя столбца не задать, PostgreSQL выберет его автоматически. Если выражение столбца представляет собой просто ссылку на столбец, то выбранное таким образом имя будет совпадать с именем столбца. В более сложных случаях может использоваться имя функции или типа, либо в отсутствие других вариантов система может сгенерировать имя вроде ?column?.

По имени выходного столбца можно обратиться к его значению в предложениях ORDER BY и GROUP BY, но не в WHERE или HAVING; в них вместо имени надо записывать всё выражение.

Вместо выражения в выходном списке можно указать *, что будет обозначать все столбцы выбранных строк. Кроме того, можно записать имя_таблицы.* как краткое обозначение всех столбцов, получаемых из данной таблицы. В этих случаях нельзя задать новые имена столбцов с помощью AS; именами выходных столбцов будут имена столбцов в таблице.

Согласно стандарту SQL, выражения в выходном списке должны вычисляться до применения DISTINCT, ORDER BY или LIMIT. Это, очевидно, необходимо для DISTINCT, так как иначе не будет ясно, какие значения должны выдаваться как уникальные. Однако во многих случаях выходные выражения удобнее вычислять после ORDER BY и LIMIT; в частности, если в выходном списке содержатся изменчивые или дорогостоящие функции. В этом случае порядок вычисления функций оказывается более интуитивным, а для строк, которые не попадут в результат, не будут производиться вычисления. PostgreSQL фактически будет вычислять выходные выражения после сортировки и ограничения их количества, если эти выражения не фигурируют в DISTINCT, ORDER BY или GROUP BY. (Например, в запросе SELECT f(x) FROM tab ORDER BY 1 функция f(x), несомненно, должна вычисляться перед сортировкой.) Выходные выражения, содержащие функции, возвращающие множества, фактически вычисляются после сортировки и до ограничения количества строк, так что LIMIT будет отбрасывать строки, выдаваемые функцией, возвращающей множество.

Предложение DISTINCT

Если указано SELECT DISTINCT, все повторяющиеся строки исключаются из результирующего набора (из каждой группы дубликатов остаётся одна строка). SELECT ALL делает противоположное: сохраняет все строки; это поведение по умолчанию.

SELECT DISTINCT ON ( выражение [, ...] ) сохраняет только первую строку из каждого набора строк, для которого данное выражение даёт одинаковые значения. Выражения DISTINCT ON обрабатываются по тем же правилам, что и выражения ORDER BY (см. выше). Заметьте, что «первая строка» каждого набора непредсказуема, если только не применяется предложение ORDER BY, определяющее, какие строки должны быть первыми. Например:

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

возвращает самую последнюю сводку погоды для каждого местоположения. Но если бы мы не добавили ORDER BY, чтобы значения времени убывали, мы бы получили сводки по местоположениям от непредсказуемого времени.

Выражения DISTINCT ON должны соответствовать самым левым выражениям в ORDER BY. Предложение ORDER BY обычно содержит и другие выражения, которые определяют желаемый порядок строк в каждой группе DISTINCT ON.

В настоящее время указания FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя задать вместе с DISTINCT.

Предложение UNION+

Предложение UNION имеет следующую общую форму:

оператор_SELECT UNION [ ALL | DISTINCT ] оператор_SELECT

Здесь оператор_SELECT — это любой подзапрос SELECT без предложений ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE. (ORDER BY и LIMIT можно добавить к вложенному выражению, если оно заключено в скобки. Без скобок эти предложения будут восприняты как применяемые к результату UNION, а не к выражению в его правой части.)

Оператор UNION вычисляет объединение множеств всех строк, возвращённых заданными запросами SELECT. Строка оказывается в объединении двух наборов результатов, если она присутствует минимум в одном наборе. Два оператора SELECT, представляющие прямые операнды UNION, должны выдавать одинаковое число столбцов, а типы соответствующих столбцов должны быть совместимыми.

Результат UNION не будет содержать повторяющихся строк, если не указан параметр ALL. ALL предотвращает исключение дубликатов. (Таким образом, UNION ALL обычно работает значительно быстрее, чем UNION; поэтому, везде, где возможно, следует указывать ALL.) DISTINCT можно записать явно, чтобы обозначить, что дублирующиеся строки должны удаляться (это поведение по умолчанию).

При использовании в одном запросе SELECT нескольких операторов UNION они вычисляются слева направо, если иной порядок не определяется скобками.

В настоящее время указания FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя задать ни для результата UNION, ни для любого из подзапросов UNION.

Предложение INTERSECT

Предложение INTERSECT имеет следующую общую форму:

оператор_SELECT INTERSECT [ ALL | DISTINCT ] оператор_SELECT

Здесь оператор_SELECT — это любой подзапрос SELECT без предложений ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE.

Оператор INTERSECT вычисляет пересечение множеств всех строк, возвращённых заданными запросами SELECT. Строка оказывается в пересечении двух наборов результатов, если она присутствует в обоих наборах.

Результат INTERSECT не будет содержать повторяющихся строк, если не указан параметр ALL. С параметром ALL строка, повторяющаяся m раз в левой таблице и n раз в правой, будет выдана в результирующем наборе min(m,n) раз. DISTINCT можно записать явно, чтобы обозначить, что дублирующиеся строки должны удаляться (это поведение по умолчанию).

При использовании в одном запросе SELECT нескольких операторов INTERSECT они вычисляются слева направо, если иной порядок не диктуется скобками. INTERSECT связывает свои подзапросы сильнее, чем UNION. Другими словами, A UNION B INTERSECT C будет восприниматься как A UNION (B INTERSECT C).

В настоящее время указания FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя задать ни для результата INTERSECT, ни для любого из подзапросов INTERSECT.

Предложение EXCEPT

Предложение EXCEPT имеет следующую общую форму:

оператор_SELECT EXCEPT [ ALL | DISTINCT ] оператор_SELECT

Здесь оператор_SELECT — это любой подзапрос SELECT без предложений ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE.

Оператор EXCEPT вычисляет набор строк, которые присутствуют в результате левого запроса SELECT, но отсутствуют в результате правого.

Результат EXCEPT не будет содержать повторяющихся строк, если не указан параметр ALL. С параметром ALL строка, повторяющаяся m раз в левой таблице и n раз в правой, будет выдана в результирующем наборе max(m-n,0) раз. DISTINCT можно записать явно, чтобы обозначить, что дублирующиеся строки должны удаляться (это поведение по умолчанию).

При использовании в одном запросе SELECT нескольких операторов EXCEPT они вычисляются слева направо, если иной порядок не диктуется скобками. EXCEPT связывает свои подзапросы так же сильно, как UNION.

В настоящее время указания FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя задать ни для результата EXCEPT, ни для любого из подзапросов EXCEPT.

Предложение ORDER BY

Необязательное предложение ORDER BY имеет следующую общую форму:

ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS { FIRST | LAST } ] [, ...]

Предложение ORDER BY указывает, что строки результата должны сортироваться согласно заданным выражениям. Если две строки дают равные значения для самого левого выражения, проверяется следующее выражение и т. д. Если их значения оказываются равными для всех заданных выражений, строки возвращаются в порядке, определяемом реализацией.

В качестве выражения может задаваться имя или порядковый номер выходного столбца (элемента списка SELECT), либо произвольное выражение со значениями входных столбцов.

Порядковым номером в данном случае считается последовательный номер (при нумерации слева направо) позиции выходного столбца. Возможность указать порядковый номер позволяет выполнить сортировку по столбцу, не имеющему уникального имени. В принципе это не абсолютно необходимо, так как выходному столбцу всегда можно присвоить имя, воспользовавшись предложением AS.

В предложении ORDER BY также можно использовать произвольные выражения, в том числе, и со столбцами, отсутствующими в списке результатов SELECT. Таким образом, следующий оператор вполне корректен:

SELECT name FROM distributors ORDER BY code;

Однако если ORDER BY применяется к результату UNION, INTERSECT или EXCEPT, в нём можно задать только имя или номер выходного столбца, но не выражение.

Если в качестве выражения ORDER BY задано простое имя, которому соответствует и выходной, и входной столбец, то ORDER BY будет воспринимать его как имя выходного столбца. Этот выбор противоположен тому, что делает GROUP BY в такой же ситуации. Такая несогласованность допущена для соответствия стандарту SQL.

Дополнительно после любого выражения в предложении ORDER BY можно добавить ключевое слово ASC (по возрастанию) или DESC (по убыванию). По умолчанию подразумевается ASC. Кроме того, можно задать имя специфического оператора сортировки в предложении USING. Оператор сортировки должен быть членом «меньше» или «больше» некоторого семейства операторов B-дерева. ASC обычно равнозначно USING < и DESC обычно равнозначно USING >. (Хотя создатель нестандартного типа данных может определить по-другому порядок сортировки по умолчанию и поставить ему в соответствие операторы с другими именами.)

Если указано NULLS LAST, значения NULL при сортировке оказываются после значений не NULL; с указанием NULLS FIRST значения NULL оказываются перед значениями не NULL. Если не указано ни то, ни другое, по умолчанию подразумевается NULLS LAST при явно или неявно выбранном порядке ASC, либо NULLS FIRST при порядке DESC (то есть по умолчанию считается, что значения NULL больше значений не NULL). С предложением USING порядок NULL по умолчанию зависит от того, является ли указанный оператор оператором «меньше» или «больше».

Заметьте, что параметры сортировки применяются только к тому выражению, за которым они следуют; в частности, ORDER BY x, y DESC означает не то же самое, что ORDER BY x DESC, y DESC.

Данные символьных строк сортируются согласно правилу сортировки, установленному для сортируемого столбца. При необходимости это правило можно переопределить, добавив предложение COLLATE в выражение, например так: ORDER BY mycolumn COLLATE "en_US". За дополнительными сведениями обратитесь к Подразделу 4.2.10 и Разделу 24.2.

Предложение LIMIT

Предложение LIMIT состоит из двух независимых вложенных предложений:

LIMIT { число | ALL }
OFFSET начало

Параметр число определяет максимальное количество строк, которое должно быть выдано, тогда как начало определяет, сколько строк нужно пропустить, прежде чем начать выдавать строки. Когда указаны оба значения, сначала строки пропускаются в количестве, заданном значением начало, а затем следующие строки выдаются в количестве, не превышающем значения число.

Если результатом выражения число оказывается NULL, предложение воспринимается как LIMIT ALL, т. е. число строк не ограничивается. Если начало принимает значение NULL, предложение воспринимается как OFFSET 0.

SQL:2008 вводит другой синтаксис для получения того же результата, и его так же поддерживает PostgreSQL. Он выглядит так:

OFFSET начало { ROW | ROWS }
FETCH { FIRST | NEXT } [ число ] { ROW | ROWS } { ONLY | WITH TIES }

В этом синтаксисе значение начало или число в соответствии со стандартом должно быть буквальной константой, параметром или именем переменной; PostgreSQL позволяет использовать и другие выражения, но их обычно нужно заключать в скобки во избежание неоднозначности. Если число опускается в предложении FETCH, оно принимает значение 1. С указанием WITH TIES будут возвращены дополнительные строки, с точки зрения ORDER BY совпадающие с последней строкой набора результатов; в этом случае предложение ORDER BY обязательно, а SKIP LOCKED — недопустимо. Слова ROW и ROWS, а также FIRST и NEXT являются незначащими и не влияют на поведение этих предложений. Согласно стандарту предложение OFFSET должно идти перед FETCH, если они присутствуют вместе; но PostgreSQL менее строг и допускает любой порядок.

Применяя LIMIT, имеет смысл использовать также предложение ORDER BY, чтобы строки результата выдавались в определённом порядке. Иначе будут возвращаться непредсказуемые подмножества строк запроса — вы можете запросить строки с десятой по двадцатую, но какой порядок вы имеете в виду? Порядок будет неизвестен, если не добавить ORDER BY.

Планировщик запроса учитывает ограничение LIMIT, строя план выполнения запроса, поэтому, вероятнее всего, планы (а значит и порядок строк) будут меняться при разных LIMIT и OFFSET. Таким образом, различные значения LIMIT/OFFSET, выбирающие разные подмножества результатов запроса, приведут к несогласованности результатов, если не установить предсказуемую сортировку с помощью ORDER BY. Это не ошибка, а неизбежное следствие того, что SQL не гарантирует вывод результатов запроса в некотором порядке, если порядок не определён явно предложением ORDER BY.

Возможно даже, что при повторном выполнении одного и того же запроса с LIMIT будут получены разные подмножества строк таблицы, если предложение ORDER BY не диктует выбор определённого подмножества. Опять же, это не ошибка; в данном случае детерминированность результата просто не гарантируется.

Предложение блокировки

Предложения блокировки включают в себя FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE и FOR KEY SHARE; они влияют на то, как SELECT блокирует строки, получаемые из таблицы.

Предложение блокировки имеет следующую общую форму:

FOR вариант_блокировки [ OF имя_таблицы [, ...] ] [ NOWAIT | SKIP LOCKED ]

Здесь вариант_блокировки может быть следующим:

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE

Подробнее о каждом режиме блокировки на уровне строк можно узнать в Подразделе 13.3.2.

Чтобы операция не ждала завершения других транзакций, к блокировке можно добавить указание NOWAIT или SKIP LOCKED. С NOWAIT оператор выдаёт ошибку, а не ждёт, если выбранную строку нельзя заблокировать немедленно. С указанием SKIP LOCKED выбранные строки, которые нельзя заблокировать немедленно, пропускаются. При этом формируется несогласованное представление данных, так что этот вариант не подходит для общего применения, но может использоваться для исключения блокировок при обращении множества потребителей к таблице типа очереди. Заметьте, что указания NOWAIT и SKIP LOCKED применяются только к блокировкам на уровне строк — необходимая блокировка ROW SHARE уровня таблицы запрашивается обычным способом (см. Главу 13). Если требуется запросить блокировку уровня таблицы без ожидания, можно сначала выполнить LOCK с указанием NOWAIT.

Если в предложении блокировки указаны определённые таблицы, блокироваться будут только строки, получаемые из этих таблиц; другие таблицы, задействованные в SELECT, будут прочитаны как обычно. Предложение блокировки без списка таблиц затрагивает все таблицы, задействованные в этом операторе. Если предложение блокировки применяется к представлению или подзапросу, оно затрагивает все таблицы, которые используются в представлении или подзапросе. Однако эти предложения не применяются к запросам WITH, к которым обращается основной запрос. Если требуется установить блокировку строк в запросе WITH, предложение блокировки нужно указать непосредственно в этом запросе WITH.

В случае необходимости задать для разных таблиц разное поведение блокировки, в запрос можно добавить несколько предложений. Если при этом одна и та же таблица упоминается (или неявно затрагивается) в нескольких предложениях блокировки, блокировка устанавливается так, как если бы было указано только одно, самое сильное из них. Подобным образом, если в одном из предложений указано NOWAIT, для этой таблицы блокировка будет запрашиваться без ожидания. В противном случае она будет обработана в режиме SKIP LOCKED, если он выбран в любом из затрагивающих её предложений.

Предложения блокировки не могут применяться в контекстах, где возвращаемые строки нельзя чётко связать с отдельными строками таблицы; например, блокировка неприменима при агрегировании.

Когда предложение блокировки находится на верхнем уровне запроса SELECT, блокируются именно те строки, которые возвращаются запросом; в случае с запросом объединения, блокировке подлежат строки, из которых составляются возвращаемые строки объединения. В дополнение к этому, заблокированы будут строки, удовлетворяющие условиям запроса на момент создания снимка запроса, хотя они не будут возвращены, если с момента снимка они изменятся и перестанут удовлетворять условиям. Если применяется LIMIT, блокировка прекращается, как только будет получено достаточное количество строк для удовлетворения лимита (но заметьте, что строки, пропускаемые указанием OFFSET, будут блокироваться). Подобным образом, если предложение блокировки применяется в запросе курсора, блокироваться будут только строки, фактически полученные или пройденные курсором.

Когда предложение блокировки находится в подзапросе SELECT, блокировке подлежат те строки, которые будет получены внешним запросом от подзапроса. Таких строк может оказаться меньше, чем можно было бы предположить, проанализировав только сам подзапрос, так как условия из внешнего запроса могут способствовать оптимизации выполнения подзапроса. Например, запрос

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

заблокирует только строки, в которых col1 = 5, при том, что в такой записи условие не относится к подзапросу.

Предыдущие версии не могли сохранить блокировку, которая была повышена последующей точкой сохранения. Например, этот код:

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

не мог сохранить блокировку FOR UPDATE после ROLLBACK TO. Это было исправлено в версии 9.3.

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

Команда TABLE

Команда

TABLE имя

равнозначна

SELECT * FROM имя

Её можно применять в качестве команды верхнего уровня или как более краткую запись внутри сложных запросов. С командой TABLE могут использоваться только предложения WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH и предложения блокировки FOR; предложение WHERE и какие-либо формы агрегирования не поддерживаются.

Необязательное предложение FROM

PostgreSQL разрешает опустить предложение FROM. Это позволяет очень легко вычислять результаты простых выражений:

SELECT 2+2;

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

Некоторые другие базы данных SQL не допускают этого, требуя задействовать в SELECT фиктивную таблицу с одной строкой.

Пустые списки SELECT

Список выходных выражений после SELECT может быть пустым, что в результате даст таблицу без столбцов. Стандарт SQL не считает такой синтаксис допустимым, но PostgreSQL допускает его, так как это согласуется с возможностью иметь таблицы с нулём столбцов. Однако когда используется DISTINCT, пустой список не допускается.

Необязательное ключевое слово AS

В стандарте SQL необязательное ключевое слово AS можно опустить перед именем выходного столбца, если это имя является допустимым именем столбца (то есть не совпадает с каким-либо зарезервированным ключевым словом). PostgreSQL несколько более строг: AS требуется, если имя столбца совпадает с любым ключевым словом, зарезервированным или нет. Тем не менее рекомендуется использовать AS или заключать имена выходных столбцов в кавычки, во избежание конфликтов, возможных при появлении в будущем новых ключевых слов.

В списке FROM и стандарт, и PostgreSQL позволяют опускать AS перед псевдонимом, который является незарезервированным ключевым словом. Но для имён выходных столбцов это не подходит из-за синтаксической неоднозначности.

Неиспользование псевдонимов вложенных SELECT в предложении FROM

Согласно стандарту SQL для любого вложенного запроса SELECT в списке FROM должен быть псевдоним. В PostgreSQL псевдонимы можно не использовать.

ONLY и наследование

Стандарт SQL требует заключать в скобки имя таблицы после ONLY, например SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... PostgreSQL считает эти скобки необязательными.

PostgreSQL позволяет добавлять в конце *, чтобы явно обозначить, что дочерние таблицы включаются в рассмотрение, в отличие от поведения с ONLY. Стандарт не позволяет этого.

(Эти соображения в равной степени касаются всех SQL-команд, поддерживающих параметр ONLY.)

Ограничения предложения TABLESAMPLE

Предложение TABLESAMPLE в настоящий момент принимается только для обычных таблиц и материализованных представлений. Однако согласно стандарту SQL оно должно применяться к любым элементам списка FROM.

Вызовы функций в предложении FROM

PostgreSQL позволяет записать вызов функции непосредственно в виде элемента списка FROM. В стандарте SQL такой вызов функции требуется помещать во вложенный SELECT; то есть, запись FROM функция(...) псевдоним примерно равнозначна записи FROM LATERAL (SELECT функция(...)) псевдоним. Заметьте, что указание LATERAL считается неявным; это связано с тем, что стандарт требует поведения LATERAL для элемента UNNEST() в предложении FROM. PostgreSQL обрабатывает UNNEST() так же, как и другие функции, возвращающие множества.

Пространства имён в GROUP BY и ORDER BY

В стандарте SQL-92 предложение ORDER BY может содержать ссылки только на выходные столбцы по именам или номерам, тогда как GROUP BY может содержать выражения с именами только входных столбцов. PostgreSQL расширяет оба эти предложения, позволяя также применять другие варианты (но если возникает неоднозначность, он разрешает её согласно стандарту). PostgreSQL также позволяет задавать произвольные выражения в обоих предложениях. Заметьте, что имена, фигурирующие в выражениях, всегда будут восприниматься как имена входных, а не выходных столбцов.

В SQL:1999 и более поздних стандартах введено несколько другое определение, которое не полностью совместимо с SQL-92. Однако в большинстве случаев PostgreSQL будет интерпретировать выражение ORDER BY или GROUP BY так, как требует SQL:1999.

Функциональные зависимости

PostgreSQL распознаёт функциональную зависимость (что позволяет опускать столбцы в GROUP BY), только когда первичный ключ таблицы присутствует в списке GROUP BY. В стандарте SQL оговариваются дополнительные условия, которые следует учитывать.

LIMIT и OFFSET

Предложения LIMIT и OFFSET относятся к специфическим особенностям PostgreSQL и поддерживаются также в MySQL. В стандарте SQL:2008 для той же цели вводятся предложения OFFSET ... FETCH {FIRST|NEXT} ..., рассмотренные ранее в Предложение LIMIT. Этот синтаксис также используется в IBM DB2. (Приложения, написанные для Oracle, часто применяют обходной способ и получают эффект этих предложений, задействуя автоматически генерируемый столбец rownum, который отсутствует в PostgreSQL.)

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

Хотя указание FOR UPDATE есть в стандарте SQL, стандарт позволяет использовать его только в предложении DECLARE CURSOR. PostgreSQL допускает его использование в любом запросе SELECT, а также в подзапросах SELECT, но это является расширением. Варианты FOR NO KEY UPDATE, FOR SHARE и FOR KEY SHARE, а также указания NOWAIT и SKIP LOCKED в стандарте отсутствуют.

Изменение данных в WITH

PostgreSQL разрешает использовать INSERT, UPDATE и DELETE в качестве запросов WITH. Стандарт SQL этого не предусматривает.

Нестандартные предложения

DISTINCT ON ( ... ) — расширение стандарта SQL.

ROWS FROM( ... ) — расширение стандарта SQL.

Указания MATERIALIZED и NOT MATERIALIZED предложения WITH относятся к расширениям стандарта SQL.