INSERT

INSERT — добавить строки в таблицу

Синтаксис

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

Здесь допускается объект_конфликта:

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

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

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

Описание

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

Имена целевых столбцов могут перечисляться в любом порядке. Если список с именами столбцов отсутствует, по умолчанию целевыми столбцами становятся все столбцы заданной таблицы; либо первые N из них, если только N столбцов поступает от предложения VALUES или запроса. Значения, получаемые от предложения 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. Подробнее об этом см. Раздел 7.8 и 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).

DEFAULT VALUES

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

выражение

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

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 ON CONSTRAINT имя_ограничения. Выбор продолжит корректно работать, когда нижележащий индекс будет заменён другим более или менее равнозначным индексом методом наложения, например, с использованием CREATE UNIQUE INDEX ... CONCURRENTLY и последующим удалением заменяемого индекса.

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

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

INSERT oid число

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

Если команда 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 относится к расширениям Postgres Pro, как и возможность применять WITH с INSERT и возможность задавать альтернативное действие с ON CONFLICT. Кроме того, ситуация, когда список столбцов опущен, но не все столбцы получают значения из предложения VALUES или запроса, стандартом не допускается.

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