Синтаксис базовых операций с таблицами
Таблица — основной объект данных в 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 |
Если указана эта опция, таблица создается как временная. Временные таблицы автоматически удаляются в конце сеанса или в конце текущей транзакции (смотрите |
UNLOGGED |
Если указана эта опция, таблица создается как нежурналируемая таблица. Данные, записываемые в нежурналируемые таблицы, не записываются в журнал предзаписи (WAL). Такие таблицы работают гораздо быстрее обычных, однако они не защищены от сбоя. Содержимое нежурналируемой таблицы также не реплицируется на резервные серверы. Любые индексы, созданные в нежурналируемой таблице, автоматически удаляются из журнала |
IF NOT EXISTS |
PostgreSQL создает новую таблицу, если таблица с таким именем не существует. Когда указан этот параметр, PostgreSQL не выдает ошибку, если таблица с указанным именем находится в базе данных. Нет никакой гарантии, что существующая таблица соответствует той, которая должна была создаться |
<имя_таблицы> |
Имя новой таблицы, может включать имя схемы ( |
<имя_столбца> |
Имя столбца, который должен быть создан в новой таблице |
<тип_данных> |
Тип данных столбца, также может включать определение массива с этим типом. За дополнительными сведениями о типах данных обратитесь к статье Data types |
COMPRESSION <метод_сжатия> |
Определяет метод сжатия для столбца. Сжатие поддерживается только для типов данных переменной длины и применяется только для столбцов с режимом хранения |
COLLATE <правило_сортировки> |
Назначает правило сортировки для столбца (который должен иметь тип, поддерживающий сортировку). Если правило не указано, используется правило сортировки по умолчанию, установленное для типа данных столбца |
<ограничение_таблицы> |
Ограничения таблиц описаны в разделе Использование ограничений |
<ограничение_столбца> |
Ограничения столбцов описаны в разделе Использование ограничений |
LIKE <исходная_таблица> [<параметры_like>] |
Указывает таблицу ( Синтаксис:
Доступные опции с выражением
|
INHERITS ( <таблица_родитель> [, … ] ) |
Указывает список таблиц, от которых новая таблица автоматически наследует все столбцы. Родительские таблицы могут быть простыми таблицами или внешними таблицами. Использование Если одно и то же имя столбца существует более чем в одной родительской таблице, выдается сообщение об ошибке, если только типы данных столбцов не совпадают в каждой из родительских таблиц. Когда конфликта нет, повторяющиеся столбцы объединяются в один столбец в новой таблице. Если список имен столбцов новой таблицы содержит имя столбца, которое также унаследовано, тип данных должен соответствовать унаследованному столбцу, а определения столбцов объединяются в одно. Если в новой таблице явно указано значение по умолчанию для столбца, это значение по умолчанию переопределяет любые значения по умолчанию из унаследованных объявлений столбца. По сравнению с |
PARTITION BY {RANGE|LIST|HASH} |
Указывает стратегию секционирования (partitioning) таблицы. Созданная таблица называется секционированной таблицей. Список столбцов или выражений в круглых скобках образует ключ секционирования. Для параметров |
ON COMMIT |
Определяет поведение временных таблиц в конце блока транзакции. Возможные значения:
|
TABLESPACE |
Определяет имя табличного пространства, в котором будет создаваться новая таблица. Если оно не указано, выбирается |
В следующем примере создается таблица table1
с двумя столбцами:
CREATE TABLE table1 (
first_column text,
second_column integer
);
Первый столбец называется first_column
и имеет текстовый тип данных, второй столбец имеет имя second_column
и тип integer
. Имена таблиц и столбцов должны соответствовать синтаксису идентификаторов, описанному в статье Identifiers and keywords.
РЕКОМЕНДАЦИЯ
Когда вы создаёте много взаимосвязанных таблиц, имеет смысл заранее выбрать единый шаблон именования таблиц и столбцов. Например, решить, будут ли в именах таблиц использоваться существительные во множественном или в единственном числе.
|
Примеры
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)
);
CREATE TABLE book_location (
shelf_no integer
) INHERITS (books);
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 |
Если зависимые строки продолжают существовать при проверке ограничения, возникает ошибка. |
RESTRICT |
Предотвращает удаление или обновление связанной строки |
SET NULL |
При удалении связанных строк PostgreSQL присваивает зависимым столбцам в ссылающейся таблице значения |
SET DEFAULT |
При удалении связанных строк PostgreSQL присваивает зависимым столбцам в ссылающейся таблице значения по умолчанию |
Главным отличием вариантов NO ACTION
и RESTRICT
является то, что NO ACTION
позволяет отложить проверку в процессе транзакции, а RESTRICT
— нет.
Внешний ключ должен ссылаться на столбцы, образующие первичный ключ или ограничение уникальности.
Системные столбцы
В каждой таблице есть несколько системных столбцов, неявно определённых системой. Как следствие, их имена нельзя использовать в качестве имён пользовательских столбцов.
Name | Description |
---|---|
tableoid |
Идентификатор объекта для таблицы, содержащей строку. Этот столбец особенно полезен для запросов, имеющих дело с секционированными таблицами или иерархией наследования, так как без него сложно определить, из какой таблицы выбрана строка. Связав |
xmin |
Идентификатор транзакции (transaction ID), добавившей строку этой версии. Версия строки — это её индивидуальное состояние, при каждом изменении создаётся новая версия одной и той же логической строки |
cmin |
Номер команды (начиная с нуля) внутри транзакции, добавившей строку |
xmax |
Идентификатор транзакции, удалившей строку, или 0 для неудалённой версии строки. Значение этого столбца может быть ненулевым и для видимой версии строки. Это обычно означает, что удаляющая транзакция ещё не была зафиксирована или удаление было отменено |
cmax |
Номер команды в удаляющей транзакции или 0 |
ctid |
Физическое расположение данной версии строки в таблице. Не рекомендуется использовать |
Идентификаторы транзакций являются 32-битными. В долго функционирующей базе данных они могут пойти по кругу. Это не является критичным при правильном обслуживании базы данных. Однако полагаться на уникальность кодов транзакций в течение длительного времени (при более чем миллиарде транзакций) не следует.
Идентификаторы команд также 32-битные. Это создаёт жёсткий лимит на количество команд SQL в одной транзакции (примерно 4 миллиарда). На практике это не проблема, так как это лимит числа команд SQL, а не количества обрабатываемых строк. Кроме того, идентификатор получают только те команды, которые фактически изменяют содержимое базы данных.