Синтаксис базовых операций с таблицами

Евгения Кузина

Таблица — основной объект данных в ADPG. Она состоит из строк и столбцов. Число и порядок столбцов фиксированы, каждый столбец имеет имя и тип данных, который ограничивает набор допустимых значений. Число строк переменно — оно отражает текущее количество находящихся в ней данных. SQL не даёт никаких гарантий относительно порядка строк таблицы. При чтении таблицы строки выводятся в произвольном порядке, если только явно не требуется сортировка.

PostgreSQL включает значительное количество встроенных типов данных, которые удовлетворяют требованиям большинства приложений. Пользователи также могут определять свои собственные типы данных. Большинство встроенных типов данных имеют очевидные имена и семантику. Некоторые из часто используемых типов данных: integer для целых чисел, numeric для дробных чисел, text для символьных строк, date для дат, time для значений времени и timestamp для значений, содержащих как дату, так и время. За подробностями обратитесь к статье Data types.

Создание таблицы

Чтобы создать таблицу, используйте команду CREATE TABLE. В этой команде вам нужно указать имя новой таблицы, имена столбцов и тип данных каждого столбца. Пример синтаксиса команды CREATE TABLE приведен ниже.

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <имя_таблицы> ( [
  { <имя_столбца> <тип_данных> [ COMPRESSION <метод_сжатия> ] [ COLLATE <правило_сортировки> ] [ <ограничение_столбца> [ ... ] ]
    | <ограничение_таблицы>
    | LIKE <исходная_таблица> [ <параметры_like> ... ] }
    [, ... ]
] )
[ INHERITS ( <таблица_родитель> [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { <имя_столбца> | ( <выражение> ) } [ COLLATE <правило_сортировки> ] [ <класс_операторов> ] [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <табличное_пространство> ]
Основные выражения и аргументы

TEMPORARY or TEMP

Если указана эта опция, таблица создается как временная. Временные таблицы автоматически удаляются в конце сеанса или в конце текущей транзакции (смотрите ON COMMIT ниже)

UNLOGGED

Если указана эта опция, таблица создается как нежурналируемая таблица. Данные, записываемые в нежурналируемые таблицы, не записываются в журнал предзаписи (WAL). Такие таблицы работают гораздо быстрее обычных, однако они не защищены от сбоя. Содержимое нежурналируемой таблицы также не реплицируется на резервные серверы. Любые индексы, созданные в нежурналируемой таблице, автоматически удаляются из журнала

IF NOT EXISTS

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

<имя_таблицы>

Имя новой таблицы, может включать имя схемы (<имя_схемы>.<имя_таблицы>)

<имя_столбца>

Имя столбца, который должен быть создан в новой таблице

<тип_данных>

Тип данных столбца, также может включать определение массива с этим типом. За дополнительными сведениями о типах данных обратитесь к статье Data types

COMPRESSION <метод_сжатия>

Определяет метод сжатия для столбца. Сжатие поддерживается только для типов данных переменной длины и применяется только для столбцов с режимом хранения main или extended (о режимах хранения столбцов рассказывается в статье ALTER TABLE). Поддерживаются следующие методы сжатия: pglz и lz4. Кроме того, <метод_сжатия> может принимать значение default, означающее, что применяемый метод определяется значением default_toast_compression

COLLATE <правило_сортировки>

Назначает правило сортировки для столбца (который должен иметь тип, поддерживающий сортировку). Если правило не указано, используется правило сортировки по умолчанию, установленное для типа данных столбца

<ограничение_таблицы>

Ограничения таблиц описаны в разделе Использование ограничений

<ограничение_столбца>

Ограничения столбцов описаны в разделе Использование ограничений

LIKE <исходная_таблица> [<параметры_like>]

Указывает таблицу (<исходная_таблица>), из которой новая таблица копирует все имена столбцов, их типы данных и их ограничения NOT NULL. Новая таблица и исходная таблица после создания становятся полностью независимыми. Необязательные параметры <параметры_like> указывают, какие дополнительные свойства исходной таблицы следует скопировать. Выражение INCLUDING копирует свойство, EXCLUDING исключает его. Выражение EXCLUDING используется по умолчанию. Если к одному типу объекта относятся несколько указаний, будет применяться последнее.

Синтаксис:

{INCLUDING|EXCLUDING}{COMMENTS|COMPRESSION|CONSTRAINTS|
DEFAULTS|GENERATED|IDENTITY|INDEXES|STATISTICS|STORAGE|ALL}

Доступные опции с выражением INCLUDING перечислены ниже:

  • INCLUDING COMMENTS — копировать комментарии для столбцов, ограничений и индексов. По умолчанию комментарии не копируются — и скопированные столбцы и ограничения в новой таблице оказываются без комментариев.

  • INCLUDING COMPRESSION — копировать метод сжатия для столбцов. По умолчанию значения метода сжатия не копируются, и для новых столбцов выбирается метод сжатия по умолчанию.

  • INCLUDING CONSTRAINTS — копировать ограничения-проверки CHECK. Опция распространяется на ограничения на уровне столбцов и на уровне таблицы. Ограничения NOT NULL копируются в новую таблицу всегда.

  • INCLUDING DEFAULTS — копировать выражения значений по умолчанию в определениях столбцов. Без этого указания выражения по умолчанию не копируются, и в новой таблице скопированные столбцы получают значения по умолчанию NULL.

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

  • INCLUDING IDENTITY — копировать характеристики идентификации в определениях столбцов. Для каждого столбца в новой таблице создаётся новая последовательность, не зависящая от последовательностей, связанных со старой таблицей.

  • INCLUDING INDEXES — создавать в новой таблице индексы, ограничения PRIMARY KEY, UNIQUE и EXCLUDE, существующие в исходной таблице. Имена для новых индексов и ограничений выбираются согласно стандартным правилам, независимо от того, как назывались исходные.

  • INCLUDING STATISTICS — копировать в новую таблицу расширенную статистику.

  • INCLUDING STORAGE — копировать параметры STORAGE в определениях копируемых столбцов. По умолчанию опции хранения исключаются, вследствие чего скопированные столбцы в новой таблице получают параметры по умолчанию, определённые соответствующим типом.

  • INCLUDING ALL — является сокращённым вариантом выбора всех имеющихся отдельных параметров. После INCLUDING ALL можно дополнительно добавить предложения EXCLUDING, чтобы выбрать все параметры, за исключением некоторых.

INHERITS ( <таблица_родитель> [, …​ ] )

Указывает список таблиц, от которых новая таблица автоматически наследует все столбцы. Родительские таблицы могут быть простыми таблицами или внешними таблицами. Использование INHERITS создает постоянную связь между новой дочерней таблицей и родительскими таблицами. Модификации родительской схемы распространяются на дочернюю таблицу, и данные дочерней таблицы включаются в сканирование родительских.

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

По сравнению с LIKE использование INHERITS создает постоянную связь между новой дочерней таблицей и ее родительской таблицей. Все изменения, примененные к структуре родительской таблицы, обычно распространяются на дочернюю

PARTITION BY {RANGE|LIST|HASH}

Указывает стратегию секционирования (partitioning) таблицы. Созданная таблица называется секционированной таблицей. Список столбцов или выражений в круглых скобках образует ключ секционирования. Для параметров RANGE или HASH ключ может включать несколько столбцов или выражений. Для LIST ключ секционирования должен состоять из одного столбца или выражения. Для RANGE и LIST требуется класс оператора B-tree (<класс_операторов>), а для разделения HASH требуется класс оператора хеширования. Если класс оператора не задан явно, PostgreSQL использует класс оператора соответствующего типа по умолчанию. Секционированная таблица делится на подтаблицы, называемые партициями или секциями (partitions), которые создаются с помощью отдельных команд CREATE TABLE. Секционированная таблица остаётся пустой. Строки данных, вставленные в таблицу, направляются в секцию на основе значений столбцов или выражений в ключе секционирования. Если ни один из существующих разделов не соответствует значениям в новой строке, будет сообщено об ошибке

ON COMMIT

Определяет поведение временных таблиц в конце блока транзакции. Возможные значения:

  • PRESERVE ROWS — специальное действие в конце транзакции не выполняется. Это поведение по умолчанию.

  • DELETE ROWS — все строки в этой временной таблице будут удаляться в конце каждого блока транзакции.

  • DROP — временная таблица будет удалена в конце текущего блока транзакции.

TABLESPACE

Определяет имя табличного пространства, в котором будет создаваться новая таблица. Если оно не указано, выбирается default_tablespace для обычных или temp_tablespaces для временных таблиц

В следующем примере создается таблица table1 с двумя столбцами:

CREATE TABLE table1 (
    first_column text,
    second_column integer
);

Первый столбец называется first_column и имеет текстовый тип данных, второй столбец имеет имя second_column и тип integer. Имена таблиц и столбцов должны соответствовать синтаксису идентификаторов, описанному в статье Identifiers and keywords.

РЕКОМЕНДАЦИЯ
Когда вы создаёте много взаимосвязанных таблиц, имеет смысл заранее выбрать единый шаблон именования таблиц и столбцов. Например, решить, будут ли в именах таблиц использоваться существительные во множественном или в единственном числе.

Примеры

Создание таблицы с ограничениями NOT NULL и PRIMARY KEY
CREATE TABLE books (
    code        char(5) CONSTRAINT first_key PRIMARY KEY,
    title       varchar(40) NOT NULL,
    date_pub   date,
    genre        varchar(10)
);
Создание временной таблицы
CREATE TEMP TABLE books (
    code    char(5) CONSTRAINT firstkey PRIMARY KEY,
    title   varchar(40) NOT NULL,
    date_pub    date,
    genre   varchar(10)
);
Использование INHERITS
CREATE TABLE book_location (
    shelf_no integer
) INHERITS (books);
Использование LIKE
CREATE TABLE book_location(
    shelf_no integer,
    LIKE books INCLUDING ALL
);

Удаление таблицы

Если вам больше не нужна таблица, её можно удалить с помощью команды DROP TABLE:

DROP TABLE books;

Попытка удалить несуществующую таблицу вызывает ошибку. Чтобы избежать этого, вы можете использовать выражение IF EXISTS:

DROP TABLE IF EXISTS books;

Значения по умолчанию

Вы можете присвоить столбцу значение по умолчанию. Для этого используется ключевое слово DEFAULT. Когда создается новая строка и значение для столбца не указано, то значение заполняется указанным по умолчанию. Если значение по умолчанию не задано, используется NULL.

В определении таблицы значения по умолчанию указываются после типа данных столбца:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 49.99
);

Значение по умолчанию может быть выражением, которое вычисляется при вставке значения по умолчанию. Например, столбец типа timestamp может иметь функцию current_timestamp в качестве значения по умолчанию. Эта функция будет возвращать время вставки строки:

CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 49.99,
shipping_date timestamp DEFAULT current_timestamp
);

Генерируемые столбцы

Генерируемый столбец — это особый тип столбца, который вычисляется на базе других столбцов. PostgreSQL поддерживает только сохраняемые генерируемые столбцы. Сохраняемый генерируемый столбец вычисляется при записи и занимает место в таблице как обычный столбец.

Чтобы создать генерируемый столбец, используйте выражение GENERATED ALWAYS AS в команде CREATE TABLE. Также необходимо использовать ключевое слово STORED:

CREATE TABLE products (
    product_no integer,
    name text,
    packing_weight numeric,
    product_weight numeric GENERATED ALWAYS AS (packing_weight / 6) STORED
);

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

Существуют следующие отличия генерируемых столбцов от столбцов со значением по умолчанию:

  • Значение столбца по умолчанию вычисляется один раз, когда в таблицу впервые вставляется строка и никакое другое значение не задано. Значение же генерируемого столбца может меняться при изменении строки и не может быть переопределено.

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

  • В выражении значения по умолчанию могут вызываться volatile-функции, например, random или функции, зависящие от времени, а для генерируемых столбцов это не допускается.

Использование ограничений

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

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

Ограничение-проверка (CHECK)

Ограничение CHECK является наиболее общим типом ограничения. Он позволяет указать условие (логическое выражение) для значений столбца. Например, чтобы задать только положительные цены на товары, можно использовать:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

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

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

Вы также можете задать имя для ограничения. Это проясняет сообщения об ошибках и позволяет ссылаться на ограничение, когда нужно его изменить. Синтаксис следующий — ключевое слово CONSTRAINT, за которым следует идентификатор, и следующее за ним определение ограничения. Например:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

Ограничение CHECK также может ссылаться на несколько столбцов. Такое ограничение является ограничением таблицы и записывается как отдельный элемент в списке столбцов:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

Следует заметить, что ограничение-проверка удовлетворяется, если выражение принимает значение true или NULL. Так как результатом многих выражений с операндами NULL будет значение NULL, такие ограничения не будут препятствовать записи NULL в соответствующее столбцы. Чтобы гарантировать, что столбец не содержит значения NULL, можно использовать ограничение NOT NULL, описанное в следующем разделе.

Ограничение NOT NULL

Ограничение NOT NULL указывает, что столбец не должен принимать значение NULL.

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

Ограничение NOT NULL — это ограничение столбца. Ограничение ненулевого значения эквивалентно ограничению CHECK (<имя_столбца> IS NOT NULL), но в PostgreSQL создание явного ограничения ненулевого значения более эффективно. С точки зрения архитектуры, в большинстве баз данных большая часть столбцов должна быть помечены как NOT NULL.

Ограничение NOT NULL имеет обратное ограничение — NULL. Это означает, что столбец может содержать значение NULL, что является также поведением по умолчанию. Ограничение NULL отсутствует в стандарте SQL и не должно использоваться в портируемых приложениях.

Ограничение уникальности (UNIQUE)

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

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

Также можно указать ограничение как ограничение таблицы:

CREATE TABLE products (
    product_no integer,
    name texCt,
    price numeric,
    UNIQUE (product_no)
);

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

CREATE TABLE products (
    serial_no integer,
    product_no integer,
    name text,
    price numeric,
    CONSTRAINT unique_constraint UNIQUE (serial_no, product_no)
);

Добавление ограничения уникальности создает уникальный индекс с типом B-tree для столбца или группы столбцов, перечисленных в ограничении.

Первичный ключ

Ограничение первичного ключа указывает, что столбец или группу столбцов можно использовать в качестве уникального идентификатора для строк в таблице. Это требует, чтобы значения были одновременно уникальными и отличными от NULL. Таким образом, две таблицы со следующими определениями будут принимать одинаковые данные:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Первичный ключ может содержать несколько столбцов:

CREATE TABLE products (
    product_no integer,
    serial_no integer,
    name text,
    price numeric,
    PRIMARY KEY (product_no, serial_no)
);

При добавлении первичного ключа автоматически создаётся уникальный индекс с типом B-tree для столбца или группы столбцов, перечисленных в первичном ключе, и данные столбцы помечаются как NOT NULL.

Таблица может иметь максимум один первичный ключ. Ограничений уникальности и ограничений NOT NULL, которые функционально почти равнозначны первичным ключам, может быть несколько, но назначить ограничение первичного ключа можно только одно.

Внешний ключ

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

Например, есть таблица products:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Также есть таблица orders, в которой хранятся заказы на товары из products. Мы хотим убедиться, что таблица orders содержит только заказы товаров, которые действительно существуют. Мы определяем ограничение внешнего ключа в таблице orders, которое ссылается на таблицу products:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

С таким ограничением создать заказ со значением product_no, отсутствующим в таблице products и не равным NULL, будет невозможно. Таблицу orders называют подчинённой или ссылающейся таблицей, а products — главной или целевой. Соответственно, столбцы называют подчинённым и главным или ссылающимся и целевым.

Вы можете опустить список столбцов в команде следующим образом:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

В этом случае первичный ключ целевой таблицы используется в качестве целевого столбца.

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

CREATE TABLE table1 (
    field1 integer PRIMARY KEY,
    field2 integer,
    field3 integer,
    CONSTRAINT constraint1 FOREIGN KEY (field2, field3) REFERENCES other_table (field_a, field_b)
);

Ограничение внешнего ключа может ссылаться на ту же самую таблицу — ссылающийся на себя внешний ключ. Он позволяет реализовать структуру дерева для таблицы:

CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text
);

Для узла верхнего уровня parent_id будет равен NULL, записи с отличным от NULL parent_id будут ссылаться только на существующие строки таблицы.

Таблица может иметь более одного внешнего ключа. Этот подход используется для реализации отношений "многие ко многим" между таблицами.

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

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON UPDATE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

В таблице ниже перечислены доступные опции.

Название Описание

CASCADE

При удалении или обновлении связанных строк зависимые от них будут так же автоматически удалены или обновлены

NO ACTION

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

RESTRICT

Предотвращает удаление или обновление связанной строки

SET NULL

При удалении связанных строк PostgreSQL присваивает зависимым столбцам в ссылающейся таблице значения NULL

SET DEFAULT

При удалении связанных строк PostgreSQL присваивает зависимым столбцам в ссылающейся таблице значения по умолчанию

Главным отличием вариантов NO ACTION и RESTRICT является то, что NO ACTION позволяет отложить проверку в процессе транзакции, а RESTRICT — нет.

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

Ограничение-исключение

Ограничения-исключения гарантируют, что при сравнении любых двух строк по указанным столбцам или выражениям с помощью заданных операторов минимум одно из этих сравнений возвратит false или NULL. Например:

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

Системные столбцы

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

Name Description

tableoid

Идентификатор объекта для таблицы, содержащей строку. Этот столбец особенно полезен для запросов, имеющих дело с секционированными таблицами или иерархией наследования, так как без него сложно определить, из какой таблицы выбрана строка. Связав tableoid со столбцом oid в таблице pg_class, можно получить имя таблицы

xmin

Идентификатор транзакции (transaction ID), добавившей строку этой версии. Версия строки — это её индивидуальное состояние, при каждом изменении создаётся новая версия одной и той же логической строки

cmin

Номер команды (начиная с нуля) внутри транзакции, добавившей строку

xmax

Идентификатор транзакции, удалившей строку, или 0 для неудалённой версии строки. Значение этого столбца может быть ненулевым и для видимой версии строки. Это обычно означает, что удаляющая транзакция ещё не была зафиксирована или удаление было отменено

cmax

Номер команды в удаляющей транзакции или 0

ctid

Физическое расположение данной версии строки в таблице. Не рекомендуется использовать ctid в качестве идентификатора строки, так как значение ctid изменяется при выполнении операции VACUUM FULL. Для идентификации логических строк следует использовать первичный ключ

Идентификаторы транзакций являются 32-битными. В долго функционирующей базе данных они могут пойти по кругу. Это не является критичным при правильном обслуживании базы данных. Однако полагаться на уникальность кодов транзакций в течение длительного времени (при более чем миллиарде транзакций) не следует.

Идентификаторы команд также 32-битные. Это создаёт жёсткий лимит на количество команд SQL в одной транзакции (примерно 4 миллиарда). На практике это не проблема, так как это лимит числа команд SQL, а не количества обрабатываемых строк. Кроме того, идентификатор получают только те команды, которые фактически изменяют содержимое базы данных.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней