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.

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

Можно использовать предложение 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 объект_конфликта должен указываться.

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

Параметр действие_при_конфликте задает альтернативное действие в случае конфликта. Это может быть предложение 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.