INSERT

INSERT
Назад	Наверх	SQL Команды	Начало	Далее

---

INSERT

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 вставляет новые строки в таблицу. Можно вставить одну или несколько строк, указанных выражениями значений, или ноль или более строк, полученных из запроса.

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

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

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

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

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

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

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

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

Параметры

Вставка

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

with_query

С помощью WITH вы можете указать одно или несколько подзапросов, на которые можно ссылаться по имени в запросе INSERT. См. Раздел 7.8 и SELECT для получения подробной информации.

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

table_name

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

alias

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

column_name

Имя столбца в таблице, указанной параметром table_name. Имя столбца может быть уточнено с помощью подполя или индекса массива, если это необходимо. (Вставка только в некоторые поля составного столбца оставляет другие поля пустыми). При ссылке на столбец с использованием 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 является нормальным поведением и его указание ничего не меняет, но Tantor SE позволяет его использовать как расширение).

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 гарантирует атомарный результат INSERT или UPDATE; при отсутствии независимых ошибок, один из этих двух результатов гарантирован, даже при высокой параллельности. Это также известно как UPSERT — “UPDATE или INSERT”.

conflict_target

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

conflict_action

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

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

index_column_name

Имя столбца table_name. Используется для вывода арбитражных индексов. Следует формату CREATE INDEX. Требуется привилегия SELECT на столбец index_column_name.

index_expression

Аналогично index_column_name, но используется для вывода выражений на столбцы table_name, которые появляются в определениях индексов (а не простых столбцах). Следует формату CREATE INDEX. Требуется привилегия SELECT на любом столбце, появляющемся в index_expression.

collation

Когда указано, требует, чтобы соответствующий index_column_name или index_expression использовал определенное правило сортировки для сопоставления во время вывода. Обычно это опускается, так как правила сортировки обычно не влияют на возникновение нарушения ограничения. Следует формату CREATE INDEX.

opclass

Когда указано, требует, чтобы соответствующий index_column_name или index_expression использовал определенный класс операторов для сопоставления во время вывода. Обычно это опускается, так как семантика equality часто эквивалентна для классов операторов типа, или потому что достаточно доверять тому, что определенные уникальные индексы имеют соответствующее определение равенства. Следует формату CREATE INDEX.

index_predicate

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

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, присвоенный вставленной строке, если count был ровно один, и целевая таблица была объявлена WITH OIDS, и 0 в противном случае, но создание таблицы WITH OIDS больше не поддерживается).

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

Примечания

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

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

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

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

---

Назад	Наверх	Далее
IMPORT FOREIGN SCHEMA	Начало	LISTEN