SELECT
SELECT, TABLE, WITH — получить строки из таблицы или представления
Синтаксис
[ WITH [ RECURSIVE ]запрос_WITH
[, ...] ] SELECT [ ALL | DISTINCT [ ON (выражение
[, ...] ) ] ] [ { * |выражение
[ [ AS ]имя_результата
] } [, ...] ] [ FROMэлемент_FROM
[, ...] ] [ WHEREусловие
] [ GROUP BY [ ALL | DISTINCT ]элемент_группирования
[, ...] ] [ HAVINGусловие
] [ WINDOWимя_окна
AS (определение_окна
) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ]выборка
] [ ORDER BYвыражение
[ ASC | DESC | USINGоператор
] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT {число
| ALL } ] [ OFFSETначало
[ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [число
] { ROW | ROWS } { ONLY | WITH TIES } ] [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OFfrom_reference
[, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ] Здесь допускаетсяэлемент_FROM
: [ ONLY ]имя_таблицы
[ * ] [ [ AS ]псевдоним
[ (псевдоним_столбца
[, ...] ) ] ] [ TABLESAMPLEметод_выборки
(аргумент
[, ...] ) [ REPEATABLE (затравка
) ] ] [ LATERAL ] (выборка
) [ [ AS ]псевдоним
[ (псевдоним_столбца
[, ...] ) ] ]имя_запроса_WITH
[ [ AS ]псевдоним
[ (псевдоним_столбца
[, ...] ) ] ] [ LATERAL ]имя_функции
( [аргумент
[, ...] ] ) [ WITH ORDINALITY ] [ [ AS ]псевдоним
[ (псевдоним_столбца
[, ...] ) ] ] [ LATERAL ]имя_функции
( [аргумент
[, ...] ] ) [ AS ]псевдоним
(определение_столбца
[, ...] ) [ LATERAL ]имя_функции
( [аргумент
[, ...] ] ) AS (определение_столбца
[, ...] ) [ LATERAL ] ROWS FROM(имя_функции
( [аргумент
[, ...] ] ) [ AS (определение_столбца
[, ...] ) ] [, ...] ) [ WITH ORDINALITY ] [ [ AS ]псевдоним
[ (псевдоним_столбца
[, ...] ) ] ]элемент_FROM
тип_соединения
элемент_FROM
{ ONусловие_соединения
| USING (столбец_соединения
[, ...] ) [ ASпсевдоним_использования_соединения
] }элемент_FROM
NATURALтип_соединения
элемент_FROM
элемент_FROM
CROSS JOINэлемент_FROM
иэлемент_группирования
может быть следующим: ( )выражение
(выражение
[, ...] ) ROLLUP ( {выражение
| (выражение
[, ...] ) } [, ...] ) CUBE ( {выражение
| (выражение
[, ...] ) } [, ...] ) GROUPING SETS (элемент_группирования
[, ...] ) изапрос_WITH
:имя_запроса_WITH
[ (имя_столбца
[, ...] ) ] AS [ [ NOT ] MATERIALIZED ] (выборка
|values
|insert
|update
|delete
|merge
) [ SEARCH { BREADTH | DEPTH } FIRST BYимя_столбца
[, ...] SETимя_столбца_послед_поиска
] [ CYCLEимя_столбца
[, ...] SETимя_столбца_пометки_цикла
[ TOзначение_пометки_цикла
DEFAULTпометка_цикла_по_умолчанию
] USINGимя_столбца_пути_цикла
] TABLE [ ONLY ]имя_таблицы
[ * ]
Описание
SELECT
получает строки из множества таблиц (возможно, пустого). Общая процедура выполнения SELECT
следующая:
Выполняются все запросы в списке
WITH
. По сути они формируют временные таблицы, к которым затем можно обращаться в спискеFROM
. Запрос вWITH
без указанияNOT MATERIALIZED
выполняется только один раз, даже когда он фигурирует в спискеFROM
неоднократно. (См. Предложение WITH ниже.)Вычисляются все элементы в списке
FROM
. (Каждый элемент в спискеFROM
представляет собой реальную или виртуальную таблицу.) Если списокFROM
содержит несколько элементов, они объединяются перекрёстным соединением. (См. Предложение FROM ниже.)Если указано предложение
WHERE
, все строки, не удовлетворяющие условию, исключаются из результата. (См. Предложение WHERE ниже.)Если присутствует указание
GROUP BY
, либо в запросе вызываются агрегатные функции, вывод разделяется по группам строк, соответствующим одному или нескольким значениям, а затем вычисляются результаты агрегатных функций. Если добавлено предложениеHAVING
, оно исключает группы, не удовлетворяющие заданному условию. (См. Предложение GROUP BY и Предложение HAVING ниже.) Хотя столбцы вывода запроса номинально вычисляются на следующем шаге, на них также можно ссылаться (по имени или порядковому номеру) в предложенииGROUP BY
.Вычисляются фактические выходные строки по заданным в
SELECT
выражениям для каждой выбранной строки или группы строк. (См. Список SELECT ниже.)SELECT DISTINCT
исключает из результата повторяющиеся строки.SELECT DISTINCT ON
исключает строки, совпадающие по всем указанным выражениям.SELECT ALL
(по умолчанию) возвращает все строки результата, включая дубликаты. (См. Предложение DISTINCT ниже.)Операторы
UNION
,INTERSECT
иEXCEPT
объединяют вывод нескольких командSELECT
в один результирующий набор. ОператорUNION
возвращает все строки, представленные в одном, либо обоих наборах результатов. ОператорINTERSECT
возвращает все строки, представленные строго в обоих наборах. ОператорEXCEPT
возвращает все строки, представленные в первом наборе, но не во втором. Во всех трёх случаях повторяющиеся строки исключаются из результата, если явно не указаноALL
. Чтобы явно обозначить, что выдаваться должны только неповторяющиеся строки, можно добавить избыточное словоDISTINCT
. Заметьте, что в данном контексте по умолчанию подразумеваетсяDISTINCT
, хотя в самомSELECT
по умолчанию подразумеваетсяALL
. (См. Предложение UNION+, Предложение INTERSECT и Предложение EXCEPT ниже.)Если присутствует предложение
ORDER BY
, возвращаемые строки сортируются в указанном порядке. В отсутствиеORDER BY
строки возвращаются в том порядке, в каком системе будет проще их выдать. (См. Предложение ORDER BY> ниже.)Если указано предложение
LIMIT
(илиFETCH FIRST
) либоOFFSET
, операторSELECT
возвращает только подмножество строк результата. (См. Предложение LIMIT ниже.)Если указано
FOR UPDATE
,FOR NO KEY UPDATE
,FOR SHARE
илиFOR KEY SHARE
, операторSELECT
блокирует выбранные строки, защищая их от одновременных изменений. (См. Предложение блокировки ниже.)
Для всех столбцов, задействованных в команде SELECT
, необходимо иметь право SELECT
. Применение блокировок FOR NO KEY UPDATE
, FOR UPDATE
, FOR SHARE
или FOR KEY SHARE
требует также права UPDATE
(как минимум для одного столбца в каждой выбранной для блокировки таблице).
Параметры
Предложение WITH
Предложение WITH
позволяет задать один или несколько подзапросов, к которым затем можно обратиться по имени в основном запросе. Эти подзапросы по сути действуют как временные таблицы или представления в процессе выполнения главного запроса. Каждый подзапрос может представлять собой оператор SELECT
, TABLE
, VALUES
, INSERT
, UPDATE
, DELETE
или MERGE
. При использовании в WITH
оператора, изменяющего данные, (INSERT
, UPDATE
, DELETE
или MERGE
) обычно добавляется предложение 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
может содержать следующие элементы:
имя_таблицы
Имя (возможно, дополненное схемой) существующей таблицы или представления. Если перед именем таблицы указано
ONLY
, считывается только заданная таблица. БезONLY
считывается и заданная таблица, и все её потомки (если таковые есть). После имени таблицы можно также добавить необязательное указание*
, чтобы явно обозначить, что блокировка затрагивает и все дочерние таблицы.псевдоним
Альтернативное имя для элемента списка
FROM
. Этот псевдоним используется для краткости или для исключения неоднозначности с замкнутыми соединениями (когда одна таблица читается неоднократно). Когда задаётся псевдоним, он полностью скрывает настоящее имя таблицы или функции; например, при записиFROM foo AS f
, в продолжении запросаSELECT
к этому элементуFROM
нужно обращаться по имениf
, а неfoo
. Если задан псевдоним таблицы, за ним можно также написать список псевдонимов столбцов, который определит альтернативные имена для столбцов таблицы.TABLESAMPLE
метод_выборки
(аргумент
[, ...] ) [ REPEATABLE (затравка
) ]Предложение
TABLESAMPLE
, сопровождающееимя_таблицы
, показывает, что для получения подмножества строк в этой таблице должен применяться указанныйметод_выборки
. Эта выборка предшествует применению любых других фильтров, например, в предложенииWHERE
. В стандартный дистрибутив PostgreSQL включены два метода выборки,BERNOULLI
иSYSTEM
; другие методы выборки можно установить в базу данных через расширения.Методы выборки
BERNOULLI
иSYSTEM
принимают единственныйаргумент
, определяющий, какой процент таблицы должен попасть в выборку, от 0 до 100. Этот аргумент может задаваться любым выражением со значением типаreal
. (Другие методы выборки могут принимать дополнительные или другие параметры.) Оба этих метода возвращают случайную выборку таблицы, содержащую примерно указанный процент строк таблицы. МетодBERNOULLI
сканирует всю таблицу и выбирает или игнорирует отдельные строки независимо, с заданной вероятностью. МетодSYSTEM
строит выборку на уровне блоков, определяя для каждого блока шанс его задействовать, и возвращает все строки из каждого задействуемого блока. МетодSYSTEM
работает значительно быстрееBERNOULLI
, когда выбирается небольшой процент строк, но он может выдавать менее случайную выборку таблицы из-за эффектов кучности.В необязательном предложении
REPEATABLE
задаётсязатравка
— число или выражение, задающее отправное значение для генератора случайных чисел в методе выборки. Значением затравки может быть любое отличное от NULL число с плавающей точкой. Два запроса, в которых указаны одинаковые значения затравки иаргумента
, выдадут одну и ту же выборку таблицы при условии неизменности содержимого таблицы. Но с разными значениями затравки выборки обычно получаются разными. В отсутствие предложенияREPEATABLE
для каждого запроса выдаётся новая случайная выборка, в зависимости от затравки, сгенерированной системой. Заметьте, что некоторые дополнительные методы выборки не принимают предложениеREPEATABLE
и выдают разные выборки при каждом использовании.выборка
Предложение
FROM
может содержать вложенный запросSELECT
. Можно считать, что из его результата создаётся временная таблица на время выполнения основной командыSELECT
. Заметьте, что вложенный запросSELECT
должен заключаться в скобки и для него может задаваться псевдоним так же, как для таблицы. Здесь также можно использовать командуVALUES
.имя_запроса_WITH
На запрос
WITH
можно ссылаться по имени, как если бы имя запроса представляло имя таблицы. (На самом деле запросWITH
скрывает любую реальную таблицу с тем же именем для основного запроса. Если необходимо обратиться к одноимённой реальной таблице, можно дополнить имя этой таблицы именем схемы.) Для этого имени можно задать псевдоним, так же, как и для имени таблицы.имя_функции
В предложении
FROM
могут содержаться вызовы функций. (Это особенно полезно для функций, возвращающих множества, но в принципе можно использовать любые функции.) Можно считать, что для результата функции создаётся временная таблица на время выполнения текущей командыSELECT
. Если функция возвращает результат составного типа (это касается и функций с несколькими аргументамиOUT
), каждый атрибут результата помещается в отдельный столбец этой неявной таблицы.Когда вызов функции дополняется необязательным предложением
WITH ORDINALITY
, к столбцам результата функции добавляется столбец типаbigint
. В этом столбце нумеруются строки набора результатов функции, начиная с 1. По умолчанию ему присваивается имяordinality
.Псевдоним для функции можно задать так же, как и для таблицы. Если этот псевдоним задан, за ним можно также написать список псевдонимов столбцов, который определит альтернативные имена для атрибутов составного типа результата функции, включая имя столбца нумерации (если он присутствует).
Несколько вызовов функций можно объединить в одном элементе предложения
FROM
, заключив их в конструкциюROWS FROM( ... )
. Выводом такого элемента будет соединение первых строк всех функций, затем вторых строк и т. д. Если одни функции выдают меньше строк, чем другие, недостающие данные заменяются значениями NULL, так что общее число возвращаемых строк всегда будет равняться максимальному числу строк из возвращённых всеми функциями.Если функция определена как возвращающая тип данных
record
, для неё нужно указать псевдоним или ключевое словоAS
, за которым должен идти список определений столбцов в форме(
. Список определений столбцов должен соответствовать фактическому количеству и типу столбцов, возвращаемых функцией.имя_столбца
тип_данных
[, ... ])Если при использовании синтаксиса
ROWS FROM( ... )
одна из функций требует наличия списка определений столбцов, этот список лучше разместить после вызова функции внутриROWS FROM( ... )
. Список определений столбцов можно поместить после конструкцииROWS FROM( ... )
, только если вызывается всего одна функция, а предложениеWITH ORDINALITY
отсутствует.Чтобы использовать
ORDINALITY
со списком определений столбцов, необходимо применить записьROWS FROM( ... )
и поместить список с определениями столбцов внутрьROWS FROM( ... )
.тип_соединения
Один из следующих вариантов:
[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
Для типов соединений
INNER
иOUTER
необходимо указать условие соединения, а именно одно из предложенийON
,условие_соединения
USING (
, илистолбец_соединения
[, ...])NATURAL
. Эти предложения описываются ниже.Предложение
JOIN
объединяет два элемента спискаFROM
, которые мы для простоты дальше будем называть «таблицами», хотя на самом деле это может быть любой объект, допустимый в качестве элементаFROM
. Для определения порядка вложенности при необходимости следует использовать скобки. В отсутствие скобок предложенияJOIN
обрабатывается слева направо. В любом случаеJOIN
связывает элементы сильнее, чем запятые, разделяющие элементы в спискеFROM
. Все возможностиJOIN
существуют просто для удобства записи, они не дают ничего такого, чего нельзя было бы получить, применяя обычные предложенияFROM
иWHERE
.LEFT OUTER JOIN
возвращает все строки ограниченного декартова произведения (т. е. все объединённые строки, удовлетворяющие условию соединения) плюс все строки в таблице слева, для которых не находится строк в таблице справа, удовлетворяющих условию. Строка, взятая из таблицы слева, дополняется до полной ширины объединённой таблицы значениями NULL в столбцах таблицы справа. Заметьте, что для определения, какие строки двух таблиц соответствуют друг другу, проверяется только условие самого предложенияJOIN
. Внешние условия проверяются позже.RIGHT OUTER JOIN
, напротив, возвращает все соединённые строки плюс одну строку для каждой строки справа, не имеющей соответствия слева (эта строка дополняется значениями NULL влево). Это предложение введено исключительно для удобства записи, так как его можно легко свести кLEFT OUTER JOIN
, поменяв левую и правую таблицы местами.FULL OUTER JOIN
возвращает все соединённые строки плюс все строки слева, не имеющие соответствия справа, (дополненные значениями NULL вправо) плюс все строки справа, не имеющие соответствия слева (дополненные значениями NULL влево).ON
условие_соединения
Задаваемое
условие_соединения
представляет собой выражение, выдающее значение типаboolean
(как в предложенииWHERE
), которое определяет, какие строки считаются соответствующими при соединении.USING (
столбец_соединения
[, ...] ) [ ASпсевдоним_использования_соединения
]Предложение вида
USING ( a, b, ... )
представляет собой сокращённую форму записиON таблица_слева.a = таблица_справа.a AND таблица_слева.b = таблица_справа.b ...
. Кроме того,USING
подразумевает, что в результат соединения будет включён только один из пары равных столбцов, но не оба.Если задан
псевдоним_использования_соединения
, он определяет псевдоним таблицы со столбцами соединения. По этому имени можно будет обращаться только к столбцам соединения, перечисленным в предложенииUSING
. В отличие от обычногопсевдонима
, он не скрывает имена соединяемых таблиц от остального запроса. Также в отличие отпсевдонима
, с ним нельзя задать список псевдонимов столбцов — выходными именами столбцов соединения будут те же имена, что фигурируют в спискеUSING
.NATURAL
NATURAL
представляет собой краткую записьUSING
со списком, в котором перечисляются все столбцы двух таблиц, имеющие одинаковые имена. Если одинаковых имён нет, указаниеNATURAL
равнозначноON TRUE
.CROSS JOIN
Предложение
CROSS JOIN
равнозначноINNER JOIN ON (TRUE)
, то есть с ним никакие строки по условию не удаляются. Оба эти предложения формируют простое декартово произведение — тот же результат получится, если указать две таблицы на верхнем уровнеFROM
и добавить в качестве ограничения условие соединения (если оно есть).LATERAL
Ключевое слово
LATERAL
может предварять вложенный запросSELECT
в спискеFROM
. Оно позволяет обращаться в этом вложенномSELECT
к столбцам элементовFROM
, предшествующим ему в спискеFROM
. (БезLATERAL
все вложенные подзапросыSELECT
обрабатываются независимо и не могут ссылаться на другие элементы спискаFROM
.)Слово
LATERAL
можно также добавить перед вызовом функции в спискеFROM
, но в этом случае оно будет избыточным, так как выражения с функциями могут ссылаться на предыдущие элементы спискаFROM
в любом случае.Элемент
LATERAL
может находиться на верхнем уровне спискаFROM
или в деревеJOIN
. В последнем случае он может также ссылаться на любые элементы в левой частиJOIN
, справа от которого он находится.Когда элемент
FROM
содержит ссылкиLATERAL
, запрос выполняется следующим образом: сначала для строки элементаFROM
с целевыми столбцами, или набора строк из нескольких элементовFROM
, содержащих целевые столбцы, вычисляется элементLATERAL
со значениями этих столбцов. Затем результирующие строки обычным образом соединяются со строками, из которых они были вычислены. Эта процедура повторяется для всех строк исходных таблиц.Таблица, служащая источником столбцов, должна быть связана с элементом
LATERAL
соединениемINNER
илиLEFT
, в противном случае не образуется однозначно определяемый набор строк, из которого можно будет получать наборы строк для элементаLATERAL
. Таким образом, хотя конструкция
синтаксически правильная,X
RIGHT JOIN LATERALY
Y
в ней не может обращаться кX
.
Предложение 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
будет отбрасывать строки, выдаваемые функцией, возвращающей множество.
Примечание
В PostgreSQL до версии 9.6 никакой порядок вычисления выходных выражений по отношению к сортировке или ограничениям количества не гарантировался; он зависел от формы выбранного плана запроса.
Предложение 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 и Разделу 23.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
имя_таблицы
должно представлять собой псевдоним
или нескрытое имя_таблицы
, используемое в предложении FROM
. Подробнее о каждом режиме блокировки на уровне строк можно узнать в Подразделе 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
, работающая на уровне изоляции READ COMMITTED
и применяющая предложение ORDER BY
вместе с блокировкой, будет возвращать строки не по порядку. Это связано с тем, что ORDER BY
выполняется в первую очередь. Эта команда отсортирует результат, но затем может быть заблокирована, пытаясь получить блокировку одной или нескольких строк. К моменту, когда блокировка SELECT
будет снята, некоторые из сортируемых столбцов могут уже измениться, в результате чего их порядок может быть нарушен (хотя они были упорядочены для исходных значений). При необходимости обойти эту проблему, можно поместить FOR UPDATE/SHARE
в подзапрос, например так:
SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;
Заметьте, что в результате это приведёт к блокированию всех строк в mytable
, тогда как указание FOR UPDATE
на верхнем уровне могло бы заблокировать только фактически возвращаемые строки. Это может значительно повлиять на производительность, особенно в сочетании ORDER BY
с LIMIT
или другими ограничениями. Таким образом, этот приём рекомендуется, только если ожидается параллельное изменение сортируемых столбцов, а результат должен быть строго отсортирован.
На уровнях изоляции REPEATABLE READ
и SERIALIZABLE
это приведёт к ошибке сериализации (с SQLSTATE
'40001'
), так что на этих уровнях получить строки не по порядку невозможно.
Команда TABLE
Команда
TABLE имя
равнозначна
SELECT * FROM имя
Её можно применять в качестве команды верхнего уровня или как более краткую запись внутри сложных запросов. С командой TABLE
могут использоваться только предложения WITH
, UNION
, INTERSECT
, EXCEPT
, ORDER BY
, LIMIT
, OFFSET
, FETCH
и предложения блокировки FOR
; предложение WHERE
и какие-либо формы агрегирования не поддерживаются.
Примеры
Соединение таблицы films
с таблицей distributors
:
SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM distributors d JOIN films f USING (did); title | did | name | date_prod | kind -------------------+-----+--------------+------------+---------- The Third Man | 101 | British Lion | 1949-12-23 | Drama The African Queen | 101 | British Lion | 1951-08-11 | Romantic ...
Суммирование значений столбца len
(продолжительность) для всех фильмов и группирование результатов по столбцу kind
(типу фильма):
SELECT kind, sum(len) AS total FROM films GROUP BY kind; kind | total ----------+------- Action | 07:34 Comedy | 02:58 Drama | 14:28 Musical | 06:42 Romantic | 04:38
Суммирование значений столбца len
для всех фильмов, группирование результатов по столбцу kind
и вывод только тех групп, общая продолжительность которых меньше 5 часов:
SELECT kind, sum(len) AS total FROM films GROUP BY kind HAVING sum(len) < interval '5 hours'; kind | total ----------+------- Comedy | 02:58 Romantic | 04:38
Следующие два запроса демонстрируют равнозначные способы сортировки результатов по содержимому второго столбца (name
):
SELECT * FROM distributors ORDER BY name; SELECT * FROM distributors ORDER BY 2; did | name -----+------------------ 109 | 20th Century Fox 110 | Bavaria Atelier 101 | British Lion 107 | Columbia 102 | Jean Luc Godard 113 | Luso films 104 | Mosfilm 103 | Paramount 106 | Toho 105 | United Artists 111 | Walt Disney 112 | Warner Bros. 108 | Westward
Следующий пример показывает объединение таблиц distributors
и actors
, ограниченное именами, начинающимися с буквы W в каждой таблице. Интерес представляют только неповторяющиеся строки, поэтому ключевое слово ALL
опущено.
distributors: actors: did | name id | name -----+-------------- ----+---------------- 108 | Westward 1 | Woody Allen 111 | Walt Disney 2 | Warren Beatty 112 | Warner Bros. 3 | Walter Matthau ... ... SELECT distributors.name FROM distributors WHERE distributors.name LIKE 'W%' UNION SELECT actors.name FROM actors WHERE actors.name LIKE 'W%'; name ---------------- Walt Disney Walter Matthau Warner Bros. Warren Beatty Westward Woody Allen
Этот пример показывает, как использовать функцию в предложении FROM
, со списком определений столбцов и без него:
CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL; SELECT * FROM distributors(111); did | name -----+------------- 111 | Walt Disney CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL; SELECT * FROM distributors_2(111) AS (f1 int, f2 text); f1 | f2 -----+------------- 111 | Walt Disney
Пример функции с добавленным столбцом нумерации:
SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY; unnest | ordinality --------+---------- a | 1 b | 2 c | 3 d | 4 e | 5 f | 6 (6 rows)
Этот пример показывает, как использовать простое предложение WITH
:
WITH t AS ( SELECT random() as x FROM generate_series(1, 3) ) SELECT * FROM t UNION ALL SELECT * FROM t; x -------------------- 0.534150459803641 0.520092216785997 0.0735620250925422 0.534150459803641 0.520092216785997 0.0735620250925422
Заметьте, что запрос WITH
выполняется всего один раз, поэтому мы получаем два одинаковых набора по три случайных значения.
В этом примере WITH RECURSIVE
применяется для поиска всех подчинённых Мери (непосредственных или косвенных) и вывода их уровня косвенности в таблице с информацией только о непосредственных подчинённых:
WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS ( SELECT 1, employee_name, manager_name FROM employee WHERE manager_name = 'Mary' UNION ALL SELECT er.distance + 1, e.employee_name, e.manager_name FROM employee_recursive er, employee e WHERE er.employee_name = e.manager_name ) SELECT distance, employee_name FROM employee_recursive;
Заметьте, что это типичная форма рекурсивных запросов: начальное условие, последующий UNION
, а затем рекурсивная часть запроса. Убедитесь в том, что рекурсивная часть запроса в конце концов перестанет возвращать строки, иначе запрос окажется в бесконечном цикле. (За другими примерами обратитесь к Разделу 7.8.)
В этом примере используется LATERAL
для применения функции get_product_names()
, возвращающей множество, для каждой строки таблицы manufacturers
:
SELECT m.name AS mname, pname FROM manufacturers m, LATERAL get_product_names(m.id) pname;
Производители, с которыми в данный момент не связаны никакие продукты, не попадут в результат, так как это внутреннее соединение. Если бы мы захотели включить названия и этих производителей, мы могли бы сделать так:
SELECT m.name AS mname, pname FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;
Совместимость
Разумеется, оператор SELECT
совместим со стандартом SQL. Однако не все описанные в стандарте возможности реализованы, а некоторые, наоборот, являются расширениями.
Необязательное предложение 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
и MERGE
в качестве запросов WITH
. Стандарт SQL этого не предусматривает.
Нестандартные предложения
DISTINCT ON ( ... )
— расширение стандарта SQL.
ROWS FROM( ... )
— расширение стандарта SQL.
Указания MATERIALIZED
и NOT MATERIALIZED
предложения WITH
относятся к расширениям стандарта SQL.