Skip to main content

INSERT

note

Эта страница переведена при помощи нейросети GigaChat.

INSERT - создание новых строк в таблице.

Синтаксис​

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

Описание​

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

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

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

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

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

Необязательный параметр RETURNING позволяет вычислить и вернуть значение или значения на основе каждой реально вставленной или обновленной строки (в случае ON CONFLICT DO UPDATE). Это удобно при необходимости получить значения, задаваемые по умолчанию, например, номер секвенции SERIAL. При этом можно использовать любое выражение с использованием столбцов таблицы. Синтаксис RETURNING идентичен синтаксису списка вывода SELECT. Возвращаются только строки, которые были реально вставлены или обновлены. Например, если строка не была обновлена из‑за несоответствия условия ON CONFLICT ... WHERE, она не включается в возвращаемый результат.

Для выполнения INSERT требуется привилегия INSERT на целевую таблицу. При использовании ON CONFLICT DO UPDATE также требуется привилегия UPDATE на таблицу.

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

Использование RETURNING также требует привилегии SELECT на все упоминаемые в нем столбцы. При вставке строк из запроса необходима привилегия SELECT на все таблицы и столбцы, используемые в этом запросе.

Параметры​

Вставка​

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

with_query
Предложение WITH позволяет определить один или несколько подзапросов, доступных по имени внутри команды INSERT. Подробнее смотрите документацию по SELECT.

Если сама команда SELECT, используемая внутри INSERT, содержит свой WITH, то можно ссылаться на оба, но вложенный WITH имеет приоритет.

table_name
Имя существующей таблицы, при необходимости дополненное схемой.
alias
Замещающее имя для table_name. После его указания таблица скрывается за псевдонимом. Это полезно при ON CONFLICT DO UPDATE, чтобы избежать конфликтов имен с системной таблицей excluded.
column_name
Имя столбца в таблице table_name. Это имя столбца при необходимости может быть дополнено именем вложенного поля или индексом в массиве. Когда данные вставляются только в некоторые поля столбца составного типа, в другие поля записывается 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 является нормальным поведением и его указание ничего не делает, но PostgreSQL допускает это как расширение.

OVERRIDING USER VALUE
Если этот параметр указан, то любые значения, указанные для столбцов идентичности, игнорируются и применяются значения, сгенерированные по умолчанию последовательностью.

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

DEFAULT VALUES
Все столбцы будут заполнены значениями по умолчанию, так, как если бы DEFAULT был явно указан для каждого столбца. Предложение OVERRIDING в этой форме не допускается.
expression
Выражение или значение, которое следует присвоить соответствующему столбцу.
DEFAULT
Соответствующий столбец будет заполнен его значением по умолчанию. Столбец идентичности будет заполнен новым значением, сгенерированным связанной последовательностью. Для сгенерированного столбца указание этого разрешено, но просто указывает нормальное поведение вычисления столбца из выражения генерации.
query
Запрос (SELECT утверждение), который предоставляет строки для вставки. Обратитесь к команде SELECT, чтобы получить описание синтаксиса.
output_expression
Выражение, которое должно быть вычислено и возвращено командой INSERT после вставки или обновления каждой строки. Выражение может использовать любые имена столбцов таблицы, указанной в table_name. Используйте *, чтобы вернуть все столбцы вставленной или обновленной строки (строк).
output_name
Имя возвращаемого столбца.

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

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

conflict_target может выполнять вывод уникального индекса. При выполнении вывода он состоит из одного или нескольких столбцов index_column_name и/или выражений index_expression, а также необязательного index_predicate. Все уникальные индексы таблицы table_name, которые, независимо от порядка, содержат ровно те столбцы/выражения, что указаны в conflict_target, считаются подходящими арбитражными индексами. Если указан index_predicate, то арбитражные индексы также должны соответствовать ему как дополнительному требованию. Обратите внимание, что это означает: если доступен уникальный индекс без предиката, который удовлетворяет всем другим критериям, он будет использован при ON CONFLICT. Если попытка вывода индекса неудачна — будет вызвана ошибка.

ON CONFLICT DO UPDATE гарантирует атомарность выполнения — либо вставка, либо обновление, но не обе сразу. Если не возникло других ошибок, гарантируется одно из двух действий, даже при высокой параллельности. Такое поведение также известно как UPSERT (объединение UPDATE и INSERT).

conflict_target
Указывает, на какие конфликты ON CONFLICT должен реагировать, выбирая арбитражные индексы. Либо выполняется вывод уникального индекса, либо явно указывается ограничение. Для ON CONFLICT DO NOTHING указывать conflict_target необязательно — при его отсутствии обрабатываются конфликты со всеми применимыми ограничениями и уникальными индексами. Для ON CONFLICT DO UPDATE указание conflict_target обязательно.
conflict_action
Указывает альтернативное действие, которое следует предпринять при конфликте. Это может быть либо DO NOTHING, либо предложение DO UPDATE, которое определяет точные действия по обновлению конфликтующей строки.

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

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

index_column_name
Имя столбца в таблице table_name. Используется для вывода арбитражных индексов. Формат такой же, как в CREATE INDEX. Необходима привилегия SELECT на указанный столбец.
index_expression
Аналогично index_column_name, но используется для выражений в определениях индексов, не являющихся простыми столбцами. Формат соответствует CREATE INDEX. Требуется привилегия SELECT на все столбцы, используемые в выражении.
collation
Требует, чтобы соответствующие index_column_name или index_expression использовали определенную сортировку. Обычно не указывается, так как сортировка редко влияет на нарушения ограничений. Формат аналогичен CREATE INDEX.
opclass
Требует использование конкретного класса операторов для соответствующего index_column_name или index_expression при выводе индексов. Обычно не требуется, так как семантика равенства одинакова для большинства классов. Формат также аналогичен CREATE INDEX.
index_predicate
Используется для вывода частичных уникальных индексов. Можно выводить любой индекс, удовлетворяющий предикату, даже если он сам по себе не является частичным. Формат такой же, как в CREATE INDEX. Требуется привилегия SELECT на все используемые в выражении столбцы.
constraint_name
Указывает явно имя ограничения для использования в качестве арбитражного, вместо вывода индекса или ограничения.
condition
Задает выражение, возвращающее значение типа boolean. Обновлены будут только те строки, для которых это выражение возвращает true. Однако при выполнении действия ON CONFLICT DO UPDATE все конфликтующие строки блокируются. Обратите внимание, condition вычисляется после определения конфликта, на основании которого происходит выбор строки для обновления.

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

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

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

Совет

Предпочтительнее использовать вывод уникального индекса, а не явное указание ограничения через ON CONFLICT ON CONSTRAINT constraint_name. Вывод продолжит работать корректно, даже если исходный индекс будет заменен другим эквивалентным, например, при использовании CREATE UNIQUE INDEX ... CONCURRENTLY перед удалением старого индекса.

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

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

INSERT oid count

где count - это количество вставленных или обновленных строк, oid — всегда 0. Раньше это был OID вставленной строки при таблицах WITH OIDS, но сейчас этот режим устарел.

Если использован RETURNING, будет возвращен набор строк, аналогичный SELECT.

Примечания​

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

Также можно рассмотреть возможность использования MERGE, поскольку это позволяет объединить INSERT, UPDATE и DELETE внутри одного оператора. Смотрите MERGE.

Примеры​

Добавление одной строки в таблицу 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';

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

-- Create an empty 3x3 gameboard for noughts-and-crosses
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
-- The subscripts in the above example aren't really needed
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 позволяет ограничить набор фактически изменяемых строк (однако любая существующая строка, не подлежащая изменению, все же будет заблокирована):

-- Don't update existing distributors based in a certain ZIP code
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';

-- Name a constraint directly in the statement (uses associated
-- index to arbitrate taking the DO NOTHING action)
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

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

-- This statement could infer a partial unique index on "did"
-- with a predicate of "WHERE is_active", but it could also
-- just use a regular unique constraint on "did"
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    ON CONFLICT (did) WHERE is_active DO NOTHING;

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

INSERT соответствует стандарту SQL, за исключением следующих расширений:

  • предложение RETURNING;
  • использование WITH совместно с INSERT;
  • возможность указания альтернативного действия через ON CONFLICT.

Кроме того, стандарт SQL не допускает случай, когда список имен столбцов опущен, но при этом в VALUES или запросе указаны значения не для всех столбцов. Если нужно более соответствующее стандарту SQL утверждение, чем ON CONFLICT, смотрите MERGE.

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

Возможные ограничения условия query описаны в разделе SELECT.