Перейти к основному содержимому

SELECT

примечание

Эта страница переведена при помощи нейросети GigaChat.

SELECT - извлечение строк из таблицы или представления.

Синтаксис​

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ { * | expression [ [ AS ] output_name ] } [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item join_type from_item { ON join_condition | USING ( join_column [, ...] ) [ AS join_using_alias ] }
    from_item NATURAL join_type from_item
    from_item CROSS JOIN from_item

and grouping_element can be one of:

    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete | merge )
        [ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...] SET search_seq_col_name ]
        [ CYCLE column_name [, ...] SET cycle_mark_col_name [ TO cycle_mark_value DEFAULT cycle_mark_default ] USING cycle_path_col_name ]

TABLE [ ONLY ] table_name [ * ]

Описание​

SELECT извлекает строки из нуля или более таблиц. Общая обработка SELECT выглядит следующим образом:

  1. Все запросы в списке WITH вычисляются. Эти запросы эффективно служат временными таблицами, на которые можно ссылаться в списке FROM. Запрос WITH, на который ссылаются более одного раза в FROM, вычисляется только один раз, если не указано иное с помощью NOT MATERIALIZED. Смотрите предложение WITH ниже.
  2. Все элементы в списке FROM вычисляются. Каждый элемент в списке FROM является реальной или виртуальной таблицей. Если в списке FROM указано более одного элемента, они пересекаются вместе. Смотрите предложение FROM ниже.
  3. Если указано предложение WHERE, все строки, которые не удовлетворяют условию, исключаются из вывода. Смотрите предложение WHERE ниже.
  4. Если указано GROUP BY предложение или если есть вызовы агрегатных функций, вывод объединяется в группы строк, которые совпадают по одному или нескольким значениям, и вычисляются результаты агрегатных функций. Если присутствует HAVING предложение, оно устраняет группы, которые не удовлетворяют данному условию. Смотрите предложение GROUP BY и предложение HAVING ниже. Хотя столбцы вывода запроса номинально вычисляются на следующем шаге, они также могут быть указаны (по имени или порядковому номеру) в GROUP BY предложении.
  5. Фактические выходные строки вычисляются с использованием выражений вывода для каждой выбранной строки или группы строк. Смотрите ниже раздел «Список SELECT».
  6. SELECT DISTINCT удаляет из результата повторяющиеся строки. SELECT DISTINCT ON удаляет строки, которые совпадают по всем указанным выражениям. SELECT ALL (по умолчанию) вернет все подходящие строки, включая дубликаты. Смотрите ниже предложение DISTINCT.
  7. Используя операторы UNION, INTERSECT и EXCEPT, вывод более чем одного оператора SELECT может быть объединен для формирования единого набора результатов. Оператор UNION возвращает все строки, которые находятся в одном или обоих наборах результатов. Оператор INTERSECT возвращает все строки, которые строго находятся в обоих наборах результатов. Оператор EXCEPT возвращает строки, которые находятся в первом наборе результатов, но не во втором. Во всех трех случаях дублирующиеся строки удаляются, если явно не указано ALL. Слово шум DISTINCT можно добавить, чтобы явно указать удаление повторяющихся строк. Обратите внимание, что DISTINCT является поведением по умолчанию здесь, даже несмотря на то, что ALL является значением по умолчанию для самого SELECT. Смотрите предложения UNION, INTERSECT и EXCEPT ниже.
  8. Если указан ORDER BY раздел, возвращаемые строки сортируются в указанном порядке. Если ORDER BY не задано, строки возвращаются в том порядке, который система находит самым быстрым для создания. Смотрите предложение ORDER BY ниже.
  9. Если указано LIMIT (или FETCH FIRST) или OFFSET пункт, оператор SELECT возвращает только подмножество результирующих строк. Смотрите предложение LIMIT ниже.
  10. Если указано 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. При написании оператора изменения данных (INSERT, UPDATE, DELETE или MERGE) в WITH, обычно включается предложение RETURNING. Это результат выполнения RETURNING, а не базовой таблицы, которую изменяет оператор, который формирует временную таблицу, читаемую основным запросом. Если RETURNING опущен, оператор все равно выполняется, но он не дает никакого вывода, поэтому его нельзя использовать как таблицу в основном запросе.

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

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

non_recursive_term UNION [ ALL | DISTINCT ] recursive_term

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

Еще одним эффектом RECURSIVE является то, что запросы WITH могут быть неупорядоченными: запрос может ссылаться на другой запрос, который находится позже в списке. Но циклические ссылки или взаимная рекурсия не реализованы. Без RECURSIVE, запросы WITH могут ссылаться только на соседние запросы WITH, которые находятся раньше в списке WITH.

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

Опциональное предложение SEARCH вычисляет столбец последовательности поиска, который может применяться для упорядочивания результатов рекурсивного запроса «сначала в ширину» или «сначала в глубину». Указанный список имен столбцов задает ключ строки, который будет использоваться для отслеживания посещенных строк. Столбец с именем search_seq_col_name будет добавлен к списку результирующих столбцов запроса WITH. Этот столбец можно отсортировать во внешнем запросе для достижения соответствующего порядка.

Опциональное предложение CYCLE используется для обнаружения циклов в рекурсивных запросах. Указанный список имен столбцов задает ключ строки, который будет использоваться для отслеживания посещенных строк. Столбец с именем cycle_mark_col_name будет добавлен к списку результирующих столбцов запроса WITH. В этом столбце будет установлено значение cycle_mark_value при обнаружении цикла, иначе cycle_mark_default. Кроме того, обработка рекурсивного объединения прекратится после обнаружения цикла. cycle_mark_value и cycle_mark_default должны быть константами, они должны быть приводимы к общему типу данных, а тип данных должен иметь оператор неравенства. Стандарт SQL требует, чтобы это были булевые константы или строки символов, но PostgreSQL этого не требует. По умолчанию используются TRUE и FALSE (типа boolean). Кроме того, столбец с именем cycle_path_col_name будет добавлен к списку результирующих столбцов запроса WITH. Этот столбец используется внутренне для отслеживания посещаемых строк.

Как SEARCH, так и CYCLE разделы действительны только для рекурсивных запросов WITH. with_query должен быть 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 в качестве барьера оптимизации.

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

Предложение FROM задает одну или несколько таблиц, служащих источниками данных для SELECT. Если указано несколько источников, результатом будет их декартово произведение (cross join). Но обычно фильтрация (WHERE) применяется, чтобы сократить число возвращаемых строк.

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

table_name
Имя существующей таблицы или представления, при необходимости уточненное схемой. Если перед именем таблицы указано ONLY, то сканируется только эта таблица. Если не указано ONLY, сканируются и сама таблица, и все ее дочерние таблицы (если таковые имеются). Необязательно после имени таблицы можно указать *, чтобы явно указать, что включены дочерние таблицы.
alias
Замещающее имя для элемента FROM, содержащее псевдоним. Псевдоним используется для краткости или устранения неоднозначности при самообъединении (когда одна и та же таблица сканируется несколько раз). Когда предоставляется псевдоним, он полностью скрывает фактическое имя таблицы или функции; например, если дано FROM foo AS f, остальная часть SELECT должна ссылаться на этот элемент FROM как f, а не foo. Если написан псевдоним, также можно написать список псевдонимов столбцов, чтобы предоставить замещающие имена для одного или нескольких столбцов таблицы.
TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]
Предложение TABLESAMPLE после table_name указывает, что указанный sampling_method должен использоваться для получения подмножества строк в этой таблице. Эта выборка предшествует применению любых других фильтров, таких как предложения WHERE. Стандартное распределение PostgreSQL включает два метода выборки, BERNOULLI и SYSTEM, и другие методы выборки могут быть установлены в базе данных с помощью расширений.

Методы выборки BERNOULLI и SYSTEM каждый принимает один argument, который является долей таблицы для выборки, выраженной в процентах от 0 до 100. Этот аргумент может быть любым выражением со значением типа real. Другие методы выборки могут принимать дополнительные или другие параметры. Эти два метода возвращают случайно выбранную выборку из таблицы, которая будет содержать приблизительно указанный процент строк таблицы. Метод BERNOULLI сканирует всю таблицу и выбирает или игнорирует отдельные строки независимо с указанной вероятностью. Метод SYSTEM выполняет блочную выборку, при которой у каждого блока есть заданная вероятность выбора; все строки в каждом выбранном блоке возвращаются. Метод SYSTEM значительно быстрее, чем метод BERNOULLI, когда указаны небольшие проценты выборки, но он может вернуть менее случайную выборку из таблицы в результате кластеризации эффектов.

Опциональный REPEATABLE указывает число или выражение seed для генерации случайных чисел в методе выборки. Значение затравки может быть любым ненулевым значением с плавающей точкой. Два запроса, которые указывают одно и то же значение затравки и значения argument, выберут один и тот же образец таблицы, если таблица не была изменена тем временем. Но с разными значениями затравки выборки обычно получаются разными. Если REPEATABLE не указан выдается новая случайная выборка, в зависимости от затравки, сгенерированной системой. Заметьте, что некоторые дополнительные методы выборки не принимают предложение REPEATABLE и выдают разные выборки при каждом использовании.

select
Подзапрос может появляться в предложении FROM. Он действует так, как если бы его результат был создан как временная таблица на время выполнения этой команды SELECT. Обратите внимание, что подзапрос должен быть заключен в круглые скобки, и ему можно назначить псевдоним, так же, как для таблицы. Здесь также может использоваться команда VALUES.
with_query_name
Запрос WITH используется по имени, так, как если бы имя запроса было именем таблицы. На самом деле, такой запрос перекрывает любую реальную таблицу с тем же именем в пределах основного запроса. При необходимости, можно обратиться к реальной таблице с тем же именем, указав имя схемы. Псевдоним можно задать так же, как для таблицы.
function_name
В предложении FROM могут появляться вызовы функций. Это особенно полезно для функций, возвращающих наборы строк, но можно использовать и любые другие функции. Это работает так, как если бы результат функции был создан как временная таблица на время выполнения этого одного запроса SELECT. Если тип возвращаемого значения функции является составным (включая случай функции с несколькими параметрами OUT), каждый атрибут становится отдельным столбцом в неявной таблице.

Если к вызову функции добавлено опциональное предложение WITH ORDINALITY, к столбцам результата функции добавляется дополнительный столбец типа bigint. Этот столбец нумерует строки набора результатов функции, начиная с 1. По умолчанию этот столбец называется ordinality.

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

Несколько вызовов функций можно объединить в один элемент предложения FROM, обернув их в ROWS FROM( … ). Результатом такого элемента является последовательное объединение первой строки от каждой функции, затем второй строки от каждой функции и так далее. Если некоторые функции возвращают меньше строк, чем другие, для недостающих данных подставляются NULL, так что общее число возвращенных строк всегда совпадает с количеством строк у функции, вернувшей больше всего строк.

Если функция была определена как возвращающая тип record, то обязательно должен быть указан псевдоним или ключевое слово AS, за которым следует список определения столбцов в форме (column_name data_type [, ...]). Этот список должен точно соответствовать фактическому количеству и типам столбцов, возвращаемых функцией.

При использовании синтаксиса ROWS FROM( ... ), если одна из функций требует список определения столбцов, то предпочтительно размещать этот список после вызова функции внутри ROWS FROM( ... ). Список столбцов может быть размещен после конструкции ROWS FROM( ... ) только если внутри нее содержится только одна функция и не используется WITH ORDINALITY.

Чтобы использовать ORDINALITY вместе со списком определения столбцов, необходимо использовать синтаксис ROWS FROM( ... ) и размещать список столбцов внутри ROWS FROM( ... ).

join_type
Один из:
  • [ INNER ] JOIN;
  • LEFT [ OUTER ] JOIN;
  • RIGHT [ OUTER ] JOIN;
  • FULL [ OUTER ] JOIN.

Для типов соединения INNER и OUTER необходимо указать условие соединения, а именно одно из:

  • ON join_condition;
  • USING (join_column [, ...]);
  • NATURAL.

Предложение JOIN объединяет два элемента FROM, которые для удобства будем называть «таблицами», хотя на деле они могут быть любого поддерживаемого типа. Используйте скобки при необходимости определить порядок вложенности. При отсутствии скобок соединения JOIN группируются слева направо. В любом случае JOIN имеет более высокий приоритет, чем запятая, разделяющая элементы FROM. Все варианты JOIN являются синтаксическим удобством, так как не делают ничего такого, чего нельзя было бы добиться с помощью обычного FROM и WHERE.

LEFT OUTER JOIN возвращает все строки, удовлетворяющие условию соединения, плюс по одной строке для каждой строки из левой таблицы, для которой не нашлось подходящей строки в правой таблице. Эти строки из левой таблицы дополняются значениями NULL справа. Обратите внимание, что при определении совпадений учитывается только условие самого JOIN. Остальные условия применяются позже.

RIGHT OUTER JOIN — противоположность предыдущего: возвращаются все подходящие строки, плюс по одной строке из правой таблицы, не имевшей пары в левой таблице, с заполнением слева NULL.

FULL OUTER JOIN возвращает все совпавшие строки, а также строки из обеих таблиц, не имевшие соответствий, с заполнением недостающих значений NULL.

ON join_condition
join_condition - это выражение, дающее логическое (boolean) значение (похоже на WHERE), определяющее, какие строки в соединении считаются совпадающими.
USING ( join_column [, ...] ) [ AS join_using_alias ]
Предложение вида USING (a, b, ...) — это сокращение для ON left_table.a = right_table.a AND left_table.b = right_table.b .... Кроме того, USING подразумевает, что в выводе соединения будет включен только один столбец из каждой пары совпадающих, а не оба.

Если указано имя join_using_alias, то оно становится псевдонимом только для этих столбцов соединения, перечисленных в USING. В отличие от обычного псевдонима таблицы, он не скрывает имена объединенных таблиц в остальной части запроса. Также, в отличие от обычного псевдонима, нельзя указать список псевдонимов для столбцов — имена столбцов совпадают с теми, что указаны в списке USING.

NATURAL
NATURAL — это сокращение для списка USING, включающего все столбцы с совпадающими именами в обеих таблицах. Если таких столбцов нет, NATURAL эквивалентен ON TRUE.
CROSS JOIN
CROSS JOIN эквивалентно INNER JOIN ON (TRUE), то есть никакие строки не отбрасываются. Он дает простое декартово произведение, тот же результат, что и при перечислении таблиц на верхнем уровне FROM, но можно также указать условие соединения.
LATERAL
Ключевое слово LATERAL может предшествовать подзапросу в FROM. Это позволяет такому подзапросу ссылаться на столбцы предыдущих элементов FROM. Без LATERAL каждый подзапрос оценивается независимо и не может ссылаться на другие элементы.

LATERAL может также предшествовать вызову функции в FROM, но в этом случае это пустое слово, поскольку функции всегда могут ссылаться на предыдущие элементы FROM.

Элемент LATERAL может находиться как на верхнем уровне FROM, так и внутри дерева JOIN. В последнем случае он может ссылаться на любые элементы, расположенные слева от него.

Когда элемент FROM содержит перекрестные ссылки через LATERAL, выполнение происходит так: для каждой строки элемента FROM, предоставляющего столбцы, или набора строк, если элементов несколько, — элемент LATERAL оценивается с использованием значений этих столбцов. Полученные строки объединяются обычным образом с теми, из которых они были получены. Это повторяется для каждой строки или набора строк из таблиц-источников.

Таблицы-источники должны быть связаны с элементом LATERAL с помощью INNER или LEFT JOIN, иначе не будет определенного набора строк для вычисления LATERAL. Таким образом, конструкция вроде X RIGHT JOIN LATERAL Y синтаксически допустима, но фактически недопустима, если Y ссылается на X.

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

Опциональное предложение WHERE имеет общую форму

WHERE condition

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

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

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

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

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

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

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

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

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

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

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

Опциональное предложение HAVING имеет общую форму

HAVING condition

где condition такое же, как указано для предложения WHERE.

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

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

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

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

Опциональное предложение WINDOW имеет общую форму

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

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

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

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

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

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

Опциональный frame_clause определяет рамку окна для оконных функций, которые от нее зависят (не все такие функции зависят от рамки). Оконная рамка — это набор связанных строк для каждой строки запроса (называемой текущей строкой). Предложение frame_clause может быть следующим:

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

где frame_start и frame_end могут быть одним из

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

и frame_exclusion* может быть одним из

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

Если frame_end опущен, по умолчанию используется CURRENT ROW. Ограничения: frame_start не может быть UNBOUNDED FOLLOWING, frame_end не может быть UNBOUNDED PRECEDING, а выбор frame_end не может находиться раньше в приведенном выше списке, чем frame_start — например, RANGE BETWEEN CURRENT ROW AND offset PRECEDING недопустим.

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

frame_exclusion позволяет исключать строки из рамки, даже если они туда попадают. EXCLUDE CURRENT ROW — исключить текущую строку, EXCLUDE GROUP — исключить текущую строку и всех равных ей, EXCLUDE TIES — исключить всех равных, кроме текущей строки, EXCLUDE NO OTHERS — явно указать, что никто не исключается (по умолчанию).

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

Цель WINDOW — задать параметры работы оконных функций в списке SELECT или ORDER BY. Функции могут ссылаться на определения окна по имени через OVER. Если определение не используется — оно просто игнорируется. Можно использовать оконные функции и без WINDOW вовсе, указывая параметры прямо в OVER. WINDOW полезен, если одно и то же определение используется для нескольких функций.

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

Список SELECT​

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

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

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

Вместо выражения, * может быть записано в выходной список как сокращенное обозначение для всех столбцов выбранных строк. Также можно написать table_name.* как сокращение для столбцов, поступающих только из этой таблицы. В этих случаях невозможно указать новые имена с помощью 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 (expression [, ...]) сохраняет только первую строку каждого набора строк, где заданные выражения оцениваются как равные. Выражения DISTINCT ON интерпретируются с использованием тех же правил, что и для ORDER BY (смотрите выше). Обратите внимание, что «первая строка» каждого набора непредсказуема, если не используется ORDER BY для обеспечения того, чтобы желаемая строка появлялась первой. Например:

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

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

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

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

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

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

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement - это любое 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 может быть записано для явного указания поведения по умолчанию при устранении повторяющихся строк.

Несколько операторов UNION в том же утверждении SELECT оцениваются слева направо, если не указано иное в скобках.

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

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

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

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

select_statement - это любое утверждение 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 может быть записан для явного указания поведения по умолчанию при устранении повторяющихся строк.

Несколько операторов INTERSECT в одном SELECT оцениваются слева направо, если скобки не указывают иное. Оператор 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_statement EXCEPT [ ALL | DISTINCT ] select_statement

select_statement - это любое 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 может быть записан для явного указания поведения по умолчанию при устранении повторяющихся строк.

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

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

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

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

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

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

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

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

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

SELECT name FROM distributors ORDER BY code;

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

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

Необязательно можно добавить ключевое слово ASC (по возрастанию) или DESC (по убыванию) после любого выражения в предложении ORDER BY. Если не указано, предполагается ASC по умолчанию. В качестве альтернативы можно указать конкретное имя оператора сортировки в предложении USING. Оператор сортировки должен быть членом с меньшим или большим значением некоторой семейства операторов B-tree. ASC обычно эквивалентен USING <, а DESC обычно эквивалентен USING >. Но создатель пользовательского типа данных может определить, какая именно сортировка используется по умолчанию, и она может соответствовать операторам с другими именами.

Если указано NULLS LAST, нулевые значения сортируются после всех ненулевых значений. Если указано NULLS FIRST, нулевые значения сортируются перед всеми ненулевыми значениями. Если ни одно из них не указано, поведение по умолчанию — это NULLS LAST, когда указан ASC или подразумевается, и NULLS FIRST, когда указан DESC (таким образом, по умолчанию предполагается, что нулевые значения больше, чем ненулевые). Когда указано USING, порядок сортировки по умолчанию для нулей зависит от того, является ли оператор оператором меньше или больше.

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

Строковые данные сортируются в соответствии с сопоставлением, применяемым к столбцу, который сортируется. Это можно переопределить (при необходимости), включив предложение COLLATE в expression, например ORDER BY mycolumn COLLATE "en_US". Для получения дополнительной информации смотрите раздел «Поддержка сопоставления».

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

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

LIMIT { count | ALL }
OFFSET start

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

Если выражение count оценивается как NULL, оно обрабатывается как LIMIT ALL, то есть без ограничения. Если start оценивается как NULL, то оно обрабатывается так же, как и OFFSET 0.

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

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

В этой синтаксической конструкции значение start или count должно быть литеральной константой, параметром или именем переменной согласно стандарту. В качестве расширения PostgreSQL допускаются другие выражения, но обычно они должны быть заключены в скобки, чтобы избежать неоднозначности. Если count опущено в предложении 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 lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]

где lock_strength может быть одним из

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE

Элемент from_reference должен быть таблицей alias или непотайной колонкой table_name, указанной в предложении FROM.

Чтобы предотвратить ожидание операции для других транзакций до фиксации, используйте либо параметр NOWAIT или SKIP LOCKED. С помощью NOWAIT, инструкция сообщает об ошибке вместо ожидания, если выбранная строка не может быть заблокирована немедленно. С помощью SKIP LOCKED, любые выбранные строки, которые не могут быть заблокированы немедленно, пропускаются. Пропуск заблокированных строк обеспечивает непоследовательный просмотр данных, поэтому это не подходит для общей работы, но можно использовать для предотвращения конкуренции блокировки с несколькими потребителями, обращающимися к таблице типа очереди. Обратите внимание, что NOWAIT и SKIP LOCKED применяются только к блокировке уровня строки - требуемая ROW SHARE блокировка уровня таблицы все еще выполняется обычным образом. Если требуется запросить блокировку уровня таблицы без ожидания, можно сначала выполнить 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 name

эквивалентна

SELECT * FROM name

Она может использоваться как команда верхнего уровня или как вариант синтаксиса для экономии места в некоторых частях сложных запросов. Только предложения блокировки WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH и FOR могут быть использованы с TABLE. Предложение 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, а затем рекурсивная часть запроса. Убедитесь, что рекурсивная часть запроса в конечном итоге не вернет ни одной кортежа, иначе запрос будет выполняться бесконечно.

Этот пример использует 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 func(...) alias приблизительно эквивалентен FROM LATERAL (SELECT func (...)) alias. Обратите внимание, что 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.

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

Следующие конструкции являются расширениями стандарта SQL:

  • DISTINCT ON ( ... );
  • ROWS FROM ( ... );
  • WITH ... MATERIALIZED и WITH ... NOT MATERIALIZED.