INSERT

INSERT — создать новые строки в таблице

Синтаксис

[ WITH [ RECURSIVE ] запрос_WITH [, ...] ]
INSERT INTO имя_таблицы [ AS псевдоним ] [ ( имя_столбца [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { выражение | DEFAULT } [, ...] ) [, ...] | запрос }
    [ ON CONFLICT [ цель_конфликта ] действие_при_конфликте ]
    [ RETURNING * | выражение_результата [ [ AS ] имя_результата ] [, ...] ]

где целью_конфликта может быть:

    ( { имя_столбца_индекса | ( выражение_индекса ) } [ COLLATE правило_сортировки ] [ класс_операторов ] [, ...] ) [ WHERE предикат_индекса ]
    ON CONSTRAINT имя_ограничения

и действием_при_конфликте может быть:

    DO NOTHING
    DO UPDATE SET { имя_столбца = { выражение | DEFAULT } |
                    ( имя_столбца [, ...] ) = [ ROW ] ( { выражение | DEFAULT } [, ...] ) |
                    ( имя_столбца [, ...] ) = ( подзапрос_SELECT )
                  } [, ...]
              [ WHERE условие ]

Описание

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

Имена целевых столбцов можно перечислять в любом порядке. Если список имен столбцов вообще не задан, то по умолчанию используются все столбцы таблицы в их объявленном порядке; либо первые N из имен столбцов, если в предложении VALUES или запросе предоставляется только N столбцов. Значения, получаемые от предложения VALUES или запроса, связываются с явным или неявным списком столбцов слева направо.

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

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

Команда INSERT с таблицами без уникальных индексов не будет блокироваться параллельными операциями. Таблицы с уникальными индексами могут блокироваться, если в параллельных сеансах выполняются действия, которые блокируют или изменяют строки, соответствующие добавляемым значениям уникального индекса; подробнее это описывается в разделе Проверки уникальности индекса. Для указания альтернативного действия, заменяющего выдачу ошибки при нарушении ограничения уникальности или исключающего ограничения, можно использовать предложение ON CONFLICT. (См. Предложение ON CONFLICT ниже).

С необязательным предложением RETURNING команда INSERT вычислит и возвратит значения каждой строки, фактически добавленной (или измененной, если было использовано предложение ON CONFLICT DO UPDATE). Это в первую очередь полезно для получения значений, присвоенных по умолчанию, таких как последовательный номер записи. Однако в этом предложении можно задать любое выражение со столбцами таблицы. Список RETURNING идентичен синтаксису списка результатов SELECT. Будут возвращены только те строки, которые были успешно добавлены или изменены. Например, если строка была заблокирована, но не изменена из-за того, что условие в предложении ON CONFLICT DO UPDATE ... WHERE не было удовлетворено, строка не будет возвращена.

Чтобы добавлять строки в таблицу, необходимо иметь для нее право INSERT. Если присутствует предложение ON CONFLICT DO UPDATE, для такой таблицы также потребуется право UPDATE.

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

Для использования предложения RETURNING требуется право SELECT для всех упомянутых в RETURNING столбцов. Если для добавления строк используется запрос, то, разумеется, нужно иметь право SELECT для всех перечисленных в данном запросе таблиц или столбцов.

Параметры

Добавление

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

запрос_WITH

Предложение WITH позволяет указать один или несколько подзапросов, на которые можно ссылаться по имени в запросе INSERT. Дополнительную информацию см. в разделе Запросы WITH и на справочной странице команды SELECT.

Сам заданный запрос (оператор SELECT) также может содержать предложение WITH. В этом случае в запросе можно обращаться к обоим наборам запрос_WITH, но у второго будет приоритет, так как он вложен ближе.

имя_таблицы

Имя существующей таблицы (может быть дополнено схемой).

псевдоним

Альтернативное имя для имени_таблицы. При указании псевдонима тот полностью скрывает фактическое имя таблицы. Это особенно полезно, когда в предложении ON CONFLICT DO UPDATE фигурирует таблица с именем excluded, поскольку в противном случае это имя будет присвоено специальной таблице, представляющей строки, которые предназначены для добавления.

имя_столбца

Имя столбца в таблице с именем имя_таблицы. При необходимости имя столбца может быть дополнено именем вложенного поля или индексом в массиве. (Когда данные добавляются только в некоторые поля столбца составного типа, другие поля заполняются значением NULL.) Обращаясь к столбцу в предложении ON CONFLICT DO UPDATE, не следует включать в описание целевого столбца имя таблицы. Например, запись INSERT INTO table_name ... ON CONFLICT DO UPDATE SET table_name.col = 1 недопустима (это соответствует общему поведению команды UPDATE ).

OVERRIDING SYSTEM VALUE

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

Для столбца идентификации, определенного как GENERATED ALWAYS, добавление явного значения (кроме DEFAULT) является ошибкой, если не указано OVERRIDING SYSTEM VALUE или OVERRIDING USER VALUE. (Для столбца идентификации, определенного как GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE является обычным поведением, поэтому его указание ни на что не влияет, но QHB допускает его в качестве расширения.)

OVERRIDING USER VALUE

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

Это предложение полезно, например, при копировании значений между таблицами. Команда INSERT INTO tbl2 OVERRIDING USER VALUE SELECT \* FROM tbl1 скопирует из tbl1 все столбцы, которые не являются столбцами идентификации в tbl2, тогда как значения для столбцов идентификации в tbl2 будут генерироваться последовательностями, связанными с tbl2.

DEFAULT VALUES

Все столбцы будут заполнены значениями по умолчанию, как при для явном указании DEFAULT для каждого столбца. (Предложение OVERRIDING в этой форме не допускается.)

выражение

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

DEFAULT

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

запрос

Запрос (оператор SELECT), который выдает строки для добавления в таблицу. Описание его синтаксиса см. на справочной странице команды SELECT.

выражение_результата

Выражение, вычисляемое и возвращаемое командой INSERT после каждой строки, которая добавляется или изменяется. В этом выражении можно использовать любые имена столбцов таблицы с именем имя_таблицы. Чтобы вернуть все столбцы добавленных или измененных строк, нужно написать *.

имя_результата

Имя, используемое для возвращаемого столбца.

Предложение ON CONFLICT

Необязательное предложение ON CONFLICT задает альтернативное действие, заменяющее выдачу ошибки при нарушении уникальности или исключающего ограничения. Для каждой отдельной предложенной для добавления строки либо продолжается добавление, либо, если нарушается решающее ограничение или индекс, указанный как цель_конфликта, выполняется альтернативное действие_при_конфликте. Вариант ON CONFLICT DO NOTHING в качестве альтернативного действия просто отменяет добавление строки. Вариант ON CONFLICT DO UPDATE изменяет существующую строку, которая конфликтует со строкой, предложенной для добавления.

Задаваемая цель_конфликта может выбирать уникальный индекс. В этом случае она состоит из одного или несколько столбцов имя_столбца_индекса) и/или выражений выражение_индекса, а также необязательного предиката_индекса. В качестве решающих индексов выводятся (выбираются) все уникальные индексы в таблице имя_таблицы, которые, без учета порядка столбцов, содержат именно те столбцы/выражения, что определяют цель_конфликта. Если указывается предикат_индекса, он должен, в качестве дополнительного требования выбора, удовлетворять решающим индексам. Обратите внимание: это означает, что не частичный уникальный индекс (уникальный индекс без предиката) будет выбран (и, следовательно, будет использован в ON CONFLICT), если он удовлетворяет всем остальным доступным критериям. Если попытка выбора неудачна, выдается ошибка.

ON CONFLICT DO UPDATE гарантирует атомарный результат команды INSERT или UPDATE; при отсутствии внешней ошибки даже при высокой параллельной активности гарантируется один из этих двух результатов. Эта операция также известна как UPSERT — «UPDATE или INSERT».

цель_конфликта

Определяет, какие именно конфликты в ON CONFLICT будут решаться альтернативным действием, устанавливая решающие индексы. Это указание позволяет выполнять выбор уникального индекса либо явно задает имя ограничения. Для ON CONFLICT DO NOTHING необязательно указывать цель_конфликта; в этом случае игнорироваться будут все конфликты с любыми ограничениями (и уникальными индексами). Для ON CONFLICT DO UPDATE цель_конфликта нужно указывать обязательно.

действие_при_конфликте

Параметр действие_при_конфликте задает альтернативное действие для ON CONFLICT. Это может быть предложение DO NOTHING (не делать ничего) или DO UPDATE (произвести изменение), где указываются точные детали операции UPDATE, выполняемой в случае конфликта. Предложения SET и WHERE в ON CONFLICT DO UPDATE могут ссылаться на существующую строку с использованием имени (или псевдонима) таблицы, а также на строки, предложенные для добавления с помощью специальной таблицы excluded. Для чтения столбцов excluded необходимо иметь право SELECT для соответствующих столбцов в целевой таблице.

Обратите внимание, что эффекты действий всех триггеров BEFORE INSERT уровня строк отражаются в значениях excluded, так как эти эффекты могут привести к исключению строки из добавления.

имя_столбца_индекса

Имя столбца в таблице имя_таблицы. Используется для выбора решающих индексов. Задается в формате CREATE INDEX. Чтобы запрос выполнился, для столбца имя_столбца_индекса требуется право SELECT.

выражение_индекса

Подобно указанию имя_столбца_индекса, но используется для выбора индекса по выражениям со столбцами таблицы имя_таблицы, обозначенными в определениях индексов (а не по простым столбцам). Задается в формате CREATE INDEX. Для всех столбцов, задействованных в выражении_индекса, требуется право SELECT.

правило_сортировки

При указании устанавливает, что соответствующий столбец имя_столбца_индекса или выражение_индекса должен использовать определенный порядок сортировки для сопоставления во время выбора индекса. Обычно этот параметр опускается, так как правила сортировки в большинстве случаев не влияют на то, произойдет ли нарушение ограничений. Задается в формате CREATE INDEX.

класс_операторов

При указании устанавливает, что соответствующий столбец имя_столбца_индекса или выражение_индекса должен использовать определенный класс операторов для сопоставления во время выбора индекса. Обычно этот параметр опускается, поскольку семантика равенства все равно часто равнозначна в разных классах операторов типа, либо поскольку достаточно полагаться на то, что заданные уникальные индексы имеют адекватное определение равенства. Задается в формате CREATE INDEX.

предикат_индекса

Используется для разрешения выбора частичных уникальных индексов. Могут быть выбраны любые индексы, удовлетворяющие этому предикату (причем в действительности им необязательно быть частичными индексами). Задается в формате CREATE INDEX. Для всех столбцов, задействованных в предикате_индекса, требуется право SELECT.

имя_ограничения

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

условие

Выражение, которое выдает значение типа boolean. Будут обновлены только те строки, для которых это выражение выдает true, хотя при выборе операции ON CONFLICT DO UPDATE все строки будут заблокированы. Обратите внимание, что условие вычисляется последним, после определения конфликта в качестве кандидата на обновление.
.

Обратите внимание, что ограничения исключения не могут быть решающими в ON CONFLICT DO UPDATE. Во всех случаях в качестве решающих поддерживаются только неоткладываемые (NOT DEFERRABLE) ограничения и уникальные индексы.

Команда INSERT с предложением ON CONFLICT DO UPDATE является «детерминированной». Это означает, что команде не разрешено влиять на одну существующую строку более одного раза; в случае такой ситуации возникнет ошибка нарушения мощности множества. Строки, предлагаемые для добавления, не должны дублировать друг друга с точки зрения атрибутов, обусловленных решающим индексом или ограничением.

Обратите внимание, что в настоящее время это не работает для предложения ON CONFLICT DO UPDATE команды INSERT, применяемой к партиционированной таблице с целью изменения ключа разбиения в конфликтующей строке образом, требующим чтобы эта строка была перенесена в новую партицию.

Совет
Часто предпочтительнее вместо непосредственного указания ограничения в виде ON CONFLICT ON CONSTRAINT имя_ограничения использовать выбор уникального индекса. Выбор продолжит работать правильно, когда нижележащий индекс будет заменен другим более или менее равнозначным индексом методом наложения, например, при использовании CREATE UNIQUE INDEX ... CONCURRENTLY с последующим удалением заменяемого индекса.

Выводимая информация

После успешного завершения команда INSERT возвращает метку команды в виде

INSERT oid число

Здесь число является количеством добавленных или измененных строк. Поле oid всегда содержит 0 (раньше это был OID, назначенный добавленной строке, если число равнялось 1 и целевая таблица была объявлена указанием WITH OIDS, и 0 в противном случае, но создание таблицы WITH OIDS больше не поддерживается).

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

Примечания

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

Примеры

Добавление одной строки в таблицу films:

INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

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

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

В этом примере для столбца с датой используется предложение DEFAULT, а не указание значения:

INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

Добавление строки, полностью состоящей из значений по умолчанию:

INSERT INTO films DEFAULT VALUES;

Добавление нескольких строк с использованием многострочного синтаксиса VALUES:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

В этом примере в таблицу films добавляются некоторые строки из таблицы tmp_films, имеющей ту же структуру столбцов, что и films:

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

Этот пример демонстрирует добавление данных в столбцы с типом массива:

-- Создание пустого поля 3x3 для игры в крестики-нолики
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
-- На самом деле индексы в предыдущем примере не нужны
INSERT INTO tictactoe (game, board)
    VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');

Добавление одной строки в таблицу distributors и получение последовательного номера, сгенерированного благодаря указанию DEFAULT:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;

Увеличение счетчика продаж для продавца, занимающегося компанией Acme Corporation, и сохранение всей измененной строки вместе с текущим временем в таблице журнала:

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

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

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

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

INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')
    ON CONFLICT (did) DO NOTHING;

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

-- Не менять данные существующих дистрибьюторов в зависимости от почтового индекса
INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
    ON CONFLICT (did) DO UPDATE
    SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
    WHERE d.zipcode <> '21201';

-- Указать имя ограничения непосредственно в операторе (связанный индекс
-- применяется для принятия решения о выполнении действия DO NOTHING)
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

Добавление дистрибьютора, если возможно; в противном случае бездействие (DO NOTHING). В данном примере предполагается, что в таблице определен уникальный индекс, ограничивающий значения в столбце did по подмножеству строк, в котором логический столбец is_active вычисляется в true:

-- Этот оператор может выбрать частичный уникальный индекс по "did"
-- с предикатом "WHERE is_active", а может, просто использовать
-- обычное ограничение уникальности по столбцу "did"
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    ON CONFLICT (did) WHERE is_active DO NOTHING;

Совместимость

Команда INSERT соответствует стандарту SQL за исключением того, что предложение RETURNING является расширением QHB, как и возможность использовать WITH с INSERT, а также возможность указать альтернативное действие с помощью ON CONFLICT. Кроме того, ситуация, при которой список имен столбцов опускается, но не все столбцы получают значения из предложения VALUES или запроса, стандартом не допускается.

Стандарт SQL определяет, что предложение OVERRIDING SYSTEM VALUE можно указать только в том случае, если существует столбец идентификации, для которого всегда генерируется значение. QHB разрешает это предложение в любом случае и игнорирует его, если оно не применимо.

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

Copyright © KVANTOM LLC, 2019-2023