7.3. Списки выборки
Как говорилось в предыдущем разделе, табличное выражение в SELECT создаёт промежуточную виртуальную таблицу, возможно объединяя таблицы, представления, группируя и исключая лишние строки и т. д. Полученная таблица передаётся для обработки в список выборки. Этот список выбирает, какие колонки промежуточной таблицы должны выводиться в результате и как именно.
7.3.1. Элементы списка выборки
Простейший список выборки образует элемент *, который выбирает все колонки из полученного табличного выражения. Список выборки также может содержать список выражений значения через запятую (как определено в Разделе 4.2). Например, это может быть список имён колонок:
Имена колонок a, b и c представляют либо фактические имена колонок таблиц, перечисленных в предложении FROM, либо их псевдонимы, определённые как описано в Подразделе 7.2.1.2. Пространство имён в списке выборки то же, что и в предложении WHERE, если не используется группировка. В противном случае оно совпадает с пространством имён предложения HAVING.
Если колонка с заданным именем есть в нескольких таблицах, необходимо также указать имя таблицы, например так:
Обращаясь к нескольким таблицам, бывает удобно получить сразу все колонки одной из таблиц:
Если в списке выборки используется обычное выражение значения, по сути при этом в возвращаемую таблицу добавляется новая виртуальная колонка. Выражение значения вычисляется один раз для каждой строки результата со значениями колонок в данной строке. Хотя выражения в списке выборки не обязательно должны обращаться к колонкам табличного выражения из предложения FROM; они могут содержать, например и простые арифметические выражения.
7.3.2. Метки колонок
Элементам в списке выборки можно назначить имена для последующей обработки, например, для указания в предложении ORDER BY или для вывода в клиентском приложении. Например:
Если выходное имя колонки не определено (с помощью AS), система назначает имя сама. Для простых ссылок на колонки этим именем становится имя целевой колонки, а для вызовов функций это имя функции. Для сложных выражений система генерирует некоторое подходящее имя.
Слово AS можно опустить, но только если имя новой колонки не является ключевым словом PostgreSQL (см. Приложение C). Во избежание случайного совпадения имени с ключевым словом это имя можно заключить в кавычки. Например, VALUE — ключевое слово, поэтому такой вариант не будет работать:
Для предотвращения конфликта с ключевыми словами, которые могут появиться в будущем, рекомендуется всегда писать AS или заключать метки выходных колонок в кавычки.
Замечание: Именование выходных колонок отличается от того, что происходит в предложении FROM (см. Подраздел 7.2.1.2). Одну колонку можно переименовать дважды, но на выходе окажется имя, назначенное в списке выборки.
7.3.3. DISTINCT
После обработки списка выборки в результирующей таблице можно дополнительно исключить дублирующиеся строки. Для этого сразу после SELECT добавляется ключевое слово DISTINCT:
(Чтобы явно включить поведение по умолчанию, когда возвращаются все строки, вместо DISTINCT можно указать ключевое слово ALL.)
Две строки считаются разными, если они содержат различные значения минимум в одной колонке. При этом значения NULL полагаются равными.
Кроме того, можно явно определить, какие строки будут считаться различными, следующим образом:
Здесь выражение — обычное выражение значения, вычисляемое для всех строк. Строки, для которых перечисленные выражения дают один результат, считаются дублирующимися и возвращается только первая строка из такого набора. Заметьте, что «первая строка» набора может быть любой, если только запрос не включает сортировку, гарантирующую однозначный порядок строк, поступающих в фильтр DISTINCT. (Обработка DISTINCT ON производится после сортировки ORDER BY.)
Предложение DISTINCT ON не описано в стандарте SQL и иногда его применение считается плохим стилем из-за возможной неопределённости в результатах. При разумном использовании GROUP BY и подзапросов во FROM можно обойтись без этой конструкции, но часто она бывает удобнее.
Источник
34.16. columns
Представление columns содержит информацию обо всех колонках таблиц (или колонках представлений) в базе данных. Системные колонки ( oid и т. д.) в нём не показываются. Оно содержит только те колонки, к которым имеет доступ текущий пользователь (являясь владельцем или имея некоторые права).
Таблица 34-14. Колонки columns
| Имя | Тип данных | Описание |
|---|---|---|
| table_catalog | sql_identifier | Имя базы данных, содержащей таблицу (всегда текущая база) |
| table_schema | sql_identifier | Имя схемы, содержащей таблицу |
| table_name | sql_identifier | Имя таблицы |
| column_name | sql_identifier | Имя колонки |
| ordinal_position | cardinal_number | Порядковый номер колонки в таблице (нумерация начинается с 1) |
| column_default | character_data | Выражение по умолчанию для колонки |
| is_nullable | yes_or_no | YES, если колонка может содержать NULL, или NO, если она не принимает NULL. Не будет принимать NULL колонка с ограничением не-NULL, но возможны и другие варианты. |
| data_type | character_data | Тип данных колонки, если это встроенный тип, либо ARRAY, если это массив (в этом случае обратитесь к представлению element_types), иначе — USER-DEFINED (в этом случае тип определяется в udt_name и связанных колонках). Если колонка основана на домене, данная колонка показывает нижележащий тип домена (а сам домен показывается в domain_name и связанных колонках). |
| character_maximum_length | cardinal_number | Если в data_type указан тип текстовой или битовой строки, это поле задаёт её объявленную максимальную длину; NULL для всех других типов данных, либо если максимальная длина не объявлена. |
| character_octet_length | cardinal_number | Если в data_type указан тип символьной строки, это поле задаёт её максимально возможный размер в октетах (байтах); NULL для всех других типов данных. Максимальный размер в октетах зависит от объявленной максимальной длины в символах (см. выше) и от кодировки сервера. |
| numeric_precision | cardinal_number | Если в data_type указан числовой тип, эта колонка содержит точность (объявленную или неявную) типа для целевой колонки. Точность определяет число значащих цифр. Она может выражаться в десятичных (по основанию 10) или двоичных (по основанию 2) цифрах, согласно колонке numeric_precision_radix. Для всех других типов данных эта колонка содержит NULL. |
| numeric_precision_radix | cardinal_number | Если в data_type указан числовой тип, в этой колонке определяется, по какому основанию задаются значения в колонках numeric_precision и numeric_scale. Возможные варианты: 2 или 10. Для всех других типов данных эта колонка содержит NULL. |
| numeric_scale | cardinal_number | Если в data_type указан точный числовой тип, эта колонка содержит масштаб (объявленный или неявный) типа для целевой колонки. Масштаб определяет число значащих цифр справа от десятичной точки. Он может выражаться в десятичных (по основанию 10) или двоичных (по основанию 2) цифрах, согласно колонке numeric_precision_radix. Для всех других типов данных эта колонка содержит NULL. |
| datetime_precision | cardinal_number | Если в data_type указан тип даты, времени, отметки времени или интервала, эта колонка содержит точность (объявленную или неявную) в долях секунды типа для целевой колонки, то есть число десятичных цифр, сохраняемых после десятичной точки в значении секунд. Для всех других типов данных эта колонка содержит NULL. |
| interval_type | character_data | Если в data_type указан тип интервала, эта колонка определяет, какие поля принимает интервал в целевой колонке, например: YEAR TO MONTH, DAY TO SECOND и т. д. Если ограничения для полей не заданы (то есть, интервал принимает все поля), и для любых других типов данных это поле содержит NULL. |
| interval_precision | cardinal_number | Относится к функциональности, отсутствующей в PostgreSQL (см. поле datetime_precision, определяющее точность в долях секунды для типов интервалов) |
| character_set_catalog | sql_identifier | Относится к функциональности, отсутствующей в PostgreSQL |
| character_set_schema | sql_identifier | Относится к функциональности, отсутствующей в PostgreSQL |
| character_set_name | sql_identifier | Относится к функциональности, отсутствующей в PostgreSQL |
| collation_catalog | sql_identifier | Имя базы данных, содержащей правило сортировки колонки (это всегда текущая база), либо NULL, если это правило по умолчанию или тип данных колонки несортируемый |
| collation_schema | sql_identifier | Имя схемы, содержащей правило сортировки колонки, либо NULL, если это правило по умолчанию или тип данных колонки несортируемый |
| collation_name | sql_identifier | Имя правила сортировки колонки, либо NULL, если это правило по умолчанию или тип данных колонки несортируемый |
| domain_catalog | sql_identifier | Если целевая колонка имеет тип домена, эта колонка содержит имя базы данных, в которой определён домен (всегда текущая база), иначе NULL. |
| domain_schema | sql_identifier | Если целевая колонка имеет тип домена, эта колонка содержит имя схемы, в которой определён домен, иначе NULL. |
| domain_name | sql_identifier | Если целевая колонка имеет тип домена, эта колонка содержит имя домена, иначе NULL. |
| udt_catalog | sql_identifier | Имя базы данных, в которой определён тип (если применимо, нижележащий тип домена) колонки (всегда текущая база) |
| udt_schema | sql_identifier | Имя схемы, в которой определён тип (если применимо, нижележащий тип домена) колонки (всегда текущая база) |
| udt_name | sql_identifier | Имя типа данных колонки (если применимо, нижележащий тип домена) |
| scope_catalog | sql_identifier | Относится к функциональности, отсутствующей в PostgreSQL |
| scope_schema | sql_identifier | Относится к функциональности, отсутствующей в PostgreSQL |
| scope_name | sql_identifier | Относится к функциональности, отсутствующей в PostgreSQL |
| maximum_cardinality | cardinal_number | Всегда NULL, так как массивы имеют неограниченную максимальную ёмкость в PostgreSQL |
| dtd_identifier | sql_identifier | Идентификатор дескриптора типа данных колонки, уникальный среди всех дескрипторов типов, относящихся к таблице. Он в основном полезен для соединения с другими экземплярами таких идентификаторов. (Конкретный формат идентификатора не определён и не гарантируется, что он останется неизменным в будущих версиях.) |
| is_self_referencing | yes_or_no | Относится к функциональности, отсутствующей в PostgreSQL |
| is_identity | yes_or_no | Относится к функциональности, отсутствующей в PostgreSQL |
| identity_generation | character_data | Относится к функциональности, отсутствующей в PostgreSQL |
| identity_start | character_data | Относится к функциональности, отсутствующей в PostgreSQL |
| identity_increment | character_data | Относится к функциональности, отсутствующей в PostgreSQL |
| identity_maximum | character_data | Относится к функциональности, отсутствующей в PostgreSQL |
| identity_minimum | character_data | Относится к функциональности, отсутствующей в PostgreSQL |
| identity_cycle | yes_or_no | Относится к функциональности, отсутствующей в PostgreSQL |
| is_generated | character_data | Относится к функциональности, отсутствующей в PostgreSQL |
| generation_expression | character_data | Относится к функциональности, отсутствующей в PostgreSQL |
| is_updatable | yes_or_no | YES, если колонка допускает изменение, или NO в противном случае (колонки в базовых таблицах всегда изменяемые, но в представлениях — не обязательно) |
Так как типы данных могут определяться в SQL множеством способом и PostgreSQL добавляет дополнительные варианты, представление типов в информационной схеме может быть довольно сложным. Колонка data_type предназначена для идентификации нижележащего встроенного типа колонки. В PostgreSQL это означает, что данный тип определён в схеме системного каталога pg_catalog. Эта колонка может быть полезной, если приложение способно особым образом воспринимать встроенные типы (например, форматировать числовые типы по-другому или задействовать данные в колонках точности). Колонки udt_name, udt_schema и udt_catalog всегда указывают на нижележащий тип данных колонки, даже если колонка основана на домене. (Так как в PostgreSQL встроенные типы не отличаются от определённых пользователем, в этом представлении выводятся и они. Это расширение стандарта SQL.) Эти колонки должны учитываться, когда приложению нужно обрабатывать данные в зависимости от типа, так как в этом случае не важно, основана ли колонка на домене. Если колонка основана на домене, на него указывают колонки domain_name, domain_schema и domain_catalog. Если вы хотите связать колонки с их типами данных и обработать домены как отдельные типы, вы можете записать coalesce(domain_name, udt_name) и т. п.
Источник
5.4. Системные колонки
В каждой таблице есть несколько системных колонок, неявно определённых системой. Как следствие, их имена нельзя использовать в качестве имён пользовательских колонок. (Заметьте, что это не зависит от того, является ли имя ключевым словом или нет; заключение имени в кавычки не поможет избежать этого ограничения.) Эти колонки не должны вас беспокоить, вам лишь достаточно знать об их существовании.
Идентификатор объекта (object ID) для строки. Эта колонка присутствует, только если таблица была создана с указанием WITH OIDS, или если в момент её создания была установлена переменная конфигурации default_with_oids. Эта колонка имеет тип oid (с тем же именем, что и сама колонка); подробнее об этом типе см. Раздел 8.18. tableoid
Идентификатор объекта для таблицы, содержащей строку. Эта колонка особенно полезна для запросов, имеющих дело с иерархией наследования (см. Раздел 5.8), так как без неё сложно определить, из какой таблицы выбрана строка. Связав tableoid с колонкой oid в таблице pg_class, можно будет получить имя таблицы. xmin
Идентификатор (код) транзакции, добавившей строку этой версии. (Версия строки — это её индивидуальное состояние; при каждом изменении создаётся новая версия одной и той же логической строки.) cmin
Номер команды (начиная с нуля) внутри транзакции, добавившей строку. xmax
Идентификатор транзакции, удалившей строку, или 0 для неудалённой версии строки. Значение этой колонки может быть ненулевым и для видимой версии строки. Это обычно означает, что удаляющая транзакция ещё не была зафиксирована, или удаление было отменено. cmax
Номер команды в удаляющей транзакции или ноль. ctid
Физическое расположение данной версии строки в таблице. Заметьте, что хотя по ctid можно очень быстро найти версию строки, значение ctid изменится при выполнении VACUUM FULL. Таким образом, ctid нельзя применять в качестве долгосрочного идентификатора строки. Для идентификации логических строк лучше использовать OID или даже дополнительный последовательный номер.
Коды OID представляют собой 32-битные значения и выбираются из единого для всей СУБД счётчика. В больших или долгоживущих базах данных этот счётчик может пойти по кругу. Таким образом, не рекомендуется рассчитывать на уникальность OID, если только вы не обеспечите её дополнительно. Если вам нужно идентифицировать строки таблицы, настоятельно рекомендуется использовать последовательности. Однако можно использовать и коды OID, при выполнении следующих условий:
Когда для идентификации строк таблиц применяется OID, в каждой такой таблице должно создаваться ограничение уникальности для колонки OID. Когда такое ограничение уникальности (или уникальный индекс) существует, система позаботится о том, чтобы OID новой строки не совпал с уже существующими. (Конечно, это возможно, только если в таблице меньше 2 32 (4 миллиардов) строк, а на практике таблицы должны быть гораздо меньше, иначе может пострадать производительность системы.)
Никогда не следует рассчитывать, что OID будут уникальны среди всех таблиц; в качестве глобального идентификатора в рамках базы данных используйте комбинацию tableoid и OID строки.
Конечно, все эти таблицы должны быть созданы с указанием WITH OIDS. В PostgreSQL 8.1 и новее по умолчанию подразумевается WITHOUT OIDS.
Идентификаторы транзакций также являются 32-битными. В долгоживущей базе данных они могут пойти по кругу. Это не критично при правильном обслуживании БД; подробнее об этом см. Главу 23. Однако полагаться на уникальность кодов транзакций в течение длительного времени (при более чем миллиарде транзакций) не следует.
Источник