Запрос SQL для вставки нескольких записей (INSERT)
Помимо вставки одной записи, SQL позволяет выполнить запрос для добавления сразу группы строк в таблицу. Такой запрос будет выполняться гораздо быстрее, чем последовательное добавление по одной записи.
Этот запрос выглядит подобно запросу добавления одной записи. Отличие только в том, что указывается несколько групп данных для вставки в таблицу разделенных запятой:
INSERT INTO таблица(список полей) VALUES (список добавляемых значений 1), (список добавляемых значений 2), . (список добавляемых значений N)Каждая из таких групп указывается в отдельных скобках.
Например, вставка товаров в таблицу:
INSERT INTO goods(title, price) VALUES ("Вилка", 25), ("Столовая ложка", 35), ("Чайная ложка", 30)Такой запрос добавит в таблицу три записи с соответствующими значениями.
Все остальные правила, такие как порядок указания или обязательность полей сохраняются так же, как и для вставки одной записи в таблицу БД.
Как добавить несколько строк в таблицу sql
Добавление нескольких строк данных
Если в одном операторе INSERT нужно добавить несколько строк, то значения каждой строки перечисляются в круглых скобках, разделенных запятыми
В следующем примере в таблицу tbl_product , вставляется три строки. Так как значения для всех столбцов предоставлены и перечислены в том же порядке, что и столбцы в таблице, то имена столбцов указывать не нужно.
SQL:
INSERT INTO tbl_product VALUES
(194, ‘CENTRUM JUNIOR + EXTRA C NÄRIMISTBL N60’, 97,00, 1),
(131, ‘CENTRUM TBL N30/12 vit, 18 miner’, 61,00, 1),
(421, ‘ CENTRUM TBL N60/12 vit, 18 miner, 97,00, 1)
Оператор INSERT
Оператор INSERT вставляет новые записи в таблицу. При этом значения столбцов могут представлять собой литеральные константы, либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT ; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.
Синтаксис оператора следующий:
Как видно из представленного синтаксиса, список столбцов не является обязательным (об этом говорят квадратные скобки в описании синтаксиса). В том случае, если он отсутствует, список вставляемых значений должен быть полный, то есть обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE :
Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:
Если задать список столбцов, то можно изменить «естественный» порядок их следования:
Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы:
Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два — NULL, а последний столбец — type — PC). Теперь мы могли бы написать:
В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию — PC. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL , запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL .
Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT :
Поскольку все столбцы имеют значения по умолчанию, для вставки строки со значениями по умолчанию можно было бы написать:
Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (см. синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде
Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK , ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена. Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D все строки из таблицы Product, относящиеся к моделям персональных компьютеров (type = ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:
Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в операторе INSERT , либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов:
Как добавить несколько строк в таблицу sql
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 возвращает метку команды в виде
INSERToidчисло
Здесь число представляет количество добавленных или изменённых строк. Если число равняется одному, а целевая таблица содержит 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, ',,>');
Добавление одной строки в таблицу 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 (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 относится к расширениям PostgreSQL , как и возможность применять WITH с INSERT и возможность задавать альтернативное действие с ON CONFLICT . Кроме того, ситуация, когда список столбцов опущен, но не все столбцы получают значения из предложения VALUES или запроса , стандартом не допускается.
Возможные ограничения предложения запрос описаны в справке SELECT .
| Пред. | Наверх | След. |
| IMPORT FOREIGN SCHEMA | Начало | LISTEN |