Insert into join postgresql

Insert into join postgresql

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

Синтаксис

Описание

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 позволяет задать один или несколько подзапросов, на которые затем можно ссылаться по имени в запросе 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

Читайте также:  Linux cut last field

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

Выражение или значение, которое будет присвоено соответствующему столбцу. 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 . правило_сортировки

Читайте также:  Rdp from mac to linux

Когда задаётся, устанавливает, что соответствующие имя_столбца_индекса или выражение_индекса должны использовать определённый порядок сортировки, чтобы этот индекс мог быть выбран. Обычно это указание опускается, так как от правил сортировки чаще всего не зависит, произойдёт ли нарушение ограничений или нет. Задаётся в формате 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 возвращает метку команды в виде

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

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

Примеры

Добавление одной строки в таблицу films :

В этом примере столбец len опускается и, таким образом, получает значение по умолчанию:

В этом примере для столбца с датой задаётся указание DEFAULT , а не явное значение:

Добавление строки, полностью состоящей из значений по умолчанию:

Добавление нескольких строк с использованием многострочного синтаксиса VALUES :

В этом примере в таблицу films вставляются некоторые строки из таблицы tmp_films , имеющей ту же структуру столбцов, что и films :

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

Добавление одной строки в таблицу distributors и получение последовательного номера, сгенерированного благодаря указанию DEFAULT :

Увеличение счётчика продаж для продавца, занимающегося компанией Acme Corporation, и сохранение всей изменённой строки вместе с текущим временем в таблице журнала:

Добавить дистрибьюторов или изменить существующие данные должным образом. Предполагается, что в таблице определён уникальный индекс, ограничивающий значения в столбце did . Заметьте, что для обращения к значениям, изначально предлагаемым для добавления, используется специальная таблица excluded :

Читайте также:  Клавиша отмена на принтере кэнон

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

Добавить дистрибьюторов или изменить существующие данные должным образом. В данном примере предполагается, что в таблице определён уникальный индекс, ограничивающий значения в столбце did . Предложение WHERE позволяет ограничить набор фактически изменяемых строк (однако любая существующая строка, не подлежащая изменению, всё же будет заблокирована):

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

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

INSERT соответствует стандарту SQL, но предложение RETURNING относится к расширениям PostgreSQL , как и возможность применять WITH с INSERT и возможность задавать альтернативное действие с ON CONFLICT . Кроме того, ситуация, когда список столбцов опущен, но не все столбцы получают значения из предложения VALUES или запроса , стандартом не допускается.

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

Источник

How to modify data in PostgreSQL using INSERT, UPDATE, UPDATE JOINS, DELETE and UPSERT

SUMMARY: This article reviews how to use the basic data manipulation language (DML) types INSERT, UPDATE, UPDATE JOINS, DELETE, and UPSERT to modify data in tables.

  1. Data Manipulation Language (DML)
  2. DML Statement Types
  3. INSERT Statement
  4. UPDATE Statement
  5. UPDATE with JOINS
  6. DELETE Statement
  7. UPSERT

Data Manipulation Language (DML)

A DML statement is executed when you:

  • Add new rows to a table.
  • Modify existing rows in a table.
  • Remove existing rows from a table.

DML Statement Types

INSERT Statement

You can add new rows to a table by using the INSERT statement:

Syntax

With the above syntax, only one row is inserted at a time.

a) Insert New Rows: Insert new rows for each column.

Example

b) Insert Rows with null values:

Example

UPDATE Statement

Using an UPDATE statement a user can modify an existing row.

Syntax

Example 1

Modify a value department id to 50 for an employee whose id is 100 using the WHERE clause::

Example 2

Without the WHERE clause the entire table would get updated:

Example 3

Update MULTIPLE ROWS.

We can update more than one row using an UPDATE statement:

UPDATE JOINS

We can use UPDATE JOINS to add values from a separate table.

In the below example, we have updated the values in the second table by joining the values from the first table specifying the condition in the WHERE clause.

Update the values in the second table by joining values from the first table:

Create two tables with data:

Update the values in the second table by joining values from the first table:

DELETE Statement

The DELETE statement is used to remove existing rows from a table.

Syntax

Example 1

Delete rows by restricting a condition using a WHERE clause. If the WHERE clause is omitted, all the rows from the table would be deleted.

Example 2

A subquery will retrieve an output first and then the WHERE condition will be executed:

UPSERT STATEMENT

Using an UPSERT statement, you can update a record if it already exists or insert a new record if it does not. This can be done in a single statement.

Example

Below sample example for usage of INSERT. ON CONFLICT:

Источник

Поделиться с друзьями
КомпСовет
Adblock
detector