Оператор INSERT

Оператор INSERT вставляет новые данные в таблицу.

Примеры

Вставьте значения 1, 2, 3 в tbl.

INSERT INTO tbl
    VALUES (1), (2), (3);

Вставьте результат запроса в таблицу:

INSERT INTO tbl
    SELECT * FROM other_tbl;

Вставьте значения в столбец i, вставив значение по умолчанию в другие столбцы:

INSERT INTO tbl (i)
    VALUES (1), (2), (3);

Явно вставьте значение по умолчанию в столбец:

INSERT INTO tbl (i)
    VALUES (1), (DEFAULT), (3);

Предполагая, что tbl имеет первичный ключ/уникальное ограничение, ничего не делать при конфликте:

INSERT OR IGNORE INTO tbl (i)
    VALUES (1);

Или обновите таблицу новыми значениями вместо этого:

INSERT OR REPLACE INTO tbl (i)
    VALUES (1);

Синтаксис

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

Можно указать необязательный порядок столбцов при вставке, он может быть BY POSITION (по умолчанию) или BY NAME. Каждый столбец, не присутствующий в явном или неявном списке столбцов, будет заполнен значением по умолчанию, либо его объявленным значением по умолчанию, либо NULL, если его нет.

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

`INSERT INTO ... [BY POSITION]`

Порядок вставки значений в столбцы таблицы определяется порядком объявления столбцов. То есть, значения, предоставленные предложением VALUES или запросом, связываются со списком столбцов слева направо. Это опция по умолчанию, которую можно явно указать, используя опцию BY POSITION. Например:

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl
    VALUES (5, 42);

Указание BY POSITION необязательно и эквивалентно поведению по умолчанию:

INSERT INTO tbl
    BY POSITION
    VALUES (5, 42);

Чтобы использовать другой порядок, имена столбцов можно указать в качестве части цели, например:

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl (b, a)
    VALUES (5, 42);

Добавление BY POSITION приводит к такому же поведению:

INSERT INTO tbl
    BY POSITION (b, a)
    VALUES (5, 42);

Это вставит 5 в b и 42 в a.

`INSERT INTO ... BY NAME`

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

Например:

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl BY NAME (SELECT 42 AS b, 32 AS a);
INSERT INTO tbl BY NAME (SELECT 22 AS b);
SELECT * FROM tbl;

a	b
32	42
NULL	22

Важно отметить, что при использовании INSERT INTO ... BY NAME, имена столбцов, указанные в операторе SELECT, должны совпадать с именами столбцов в таблице. Если имя столбца написано неправильно или не существует в таблице, произойдет ошибка. Столбцы, отсутствующие в операторе SELECT, будут заполнены значением по умолчанию.

Оператор `ON CONFLICT`

Оператор ON CONFLICT может использоваться для выполнения определенного действия при возникновении конфликтов, возникающих из-за UNIQUE или PRIMARY KEY ограничений. Пример такого конфликта показан в следующем примере:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84);

Это вызывает ошибку:

Constraint Error: Duplicate key "i: 1" violates primary key constraint.

Таблица будет содержать строку, которая была вставлена первой:

SELECT * FROM tbl;

i	j
1	42

Эти сообщения об ошибках можно избежать, явно обрабатывая конфликты. DuckDB поддерживает два таких оператора: ON CONFLICT DO NOTHING и ON CONFLICT DO UPDATE SET ....

Оператор `DO NOTHING`

Оператор DO NOTHING игнорирует ошибку(и) и значения не вставляются и не обновляются. Например:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84)
    ON CONFLICT DO NOTHING;

Эти операторы завершаются успешно и оставляют таблицу со строкой <i: 1, j: 42>.

`INSERT OR IGNORE INTO`

Оператор INSERT OR IGNORE INTO ... является сокращенным синтаксисом для INSERT INTO ... ON CONFLICT DO NOTHING. Например, следующие операторы эквивалентны:

INSERT OR IGNORE INTO tbl
    VALUES (1, 84);
INSERT INTO tbl
    VALUES (1, 84) ON CONFLICT DO NOTHING;

Оператор `DO UPDATE` (Upsert)

Оператор DO UPDATE заставляет INSERT стать UPDATE для конфликтующей строки(строк) вместо этого. Выражения SET, которые следуют за ним, определяют, как эти строки обновляются. Выражения могут использовать специальную виртуальную таблицу EXCLUDED, которая содержит конфликтующие значения для строки. Необязательно, вы можете добавить дополнительный оператор WHERE, который может исключить определенные строки из обновления. Конфликты, которые не удовлетворяют этому условию, игнорируются вместо этого.

Поскольку нам нужен способ сослаться и на кортеж, который должен быть вставлен, и на кортеж, который существует, мы вводим специальный квалификатор EXCLUDED. Когда квалификатор EXCLUDED указан, ссылка относится к кортежу, который должен быть вставлен, в противном случае — к кортежу, который существует. Этот специальный квалификатор можно использовать в операторах WHERE и выражениях SET оператора ON CONFLICT.

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl VALUES (1, 42);
INSERT INTO tbl VALUES (1, 52), (1, 62) ON CONFLICT DO UPDATE SET j = EXCLUDED.j;

Примеры

Пример использования DO UPDATE:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;

i	j
1	84

Переупорядочивание столбцов и использование BY NAME также возможно:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl (j, i)
    VALUES (168, 1)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl
    BY NAME (SELECT 1 AS i, 336 AS j)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;

i	j
1	336

`INSERT OR REPLACE INTO`

Оператор INSERT OR REPLACE INTO ... — это сокращенный синтаксис для INSERT INTO ... DO UPDATE SET c1 = EXCLUDED.c1, c2 = EXCLUDED.c2, .... То есть, он обновляет каждый столбец существующей строки новыми значениями строки, которая должна быть вставлена. Например, при следующей входной таблице:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);

Эти операторы эквивалентны:

INSERT OR REPLACE INTO tbl
    VALUES (1, 84);
INSERT INTO tbl
    VALUES (1, 84)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl (j, i)
    VALUES (84, 1)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl BY NAME
    (SELECT 84 AS j, 1 AS i)
    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;

Ограничения

При использовании оператора ON CONFLICT ... DO UPDATE и возникновении конфликта, DuckDB внутренне присваивает значения NULL столбцам строки, не затронутым конфликтом, а затем снова присваивает им значения. Если затронутые столбцы используют ограничение NOT NULL, это вызовет ошибку NOT NULL constraint failed. Например:

CREATE TABLE t1 (id INTEGER PRIMARY KEY, val1 DOUBLE, val2 DOUBLE NOT NULL);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, val1 DOUBLE);
INSERT INTO t1
    VALUES (1, 2, 3);
INSERT INTO t2
    VALUES (1, 5);

INSERT INTO t1 BY NAME (SELECT id, val1 FROM t2)
    ON CONFLICT DO UPDATE
    SET val1 = EXCLUDED.val1;

Это приводит к следующей ошибке:

Constraint Error: NOT NULL constraint failed: t1.val2

Определение цели конфликта

В качестве цели конфликта может быть указан ON CONFLICT (conflict_target). Это группа столбцов, для которых определен индекс или ограничение уникальности/ключа. Если цель конфликта опущена, или PRIMARY KEY ограничение(я) в таблице является целью.

Указание цели конфликта необязательно, если не используется DO UPDATE и в таблице есть несколько ограничений уникальности/первичного ключа.

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER UNIQUE, k INTEGER);
INSERT INTO tbl
    VALUES (1, 20, 300);
SELECT * FROM tbl;

i	j	k
1	20	300

INSERT INTO tbl
    VALUES (1, 40, 700)
    ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k;

i	j	k
1	20	1400

INSERT INTO tbl
    VALUES (1, 20, 900)
    ON CONFLICT (j) DO UPDATE SET k = 5 * EXCLUDED.k;

i	j	k
1	20	4500

Когда указана цель конфликта, вы можете дополнительно отфильтровать ее с помощью оператора WHERE, который должен выполняться всеми конфликтами.

INSERT INTO tbl
    VALUES (1, 40, 700)
    ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k WHERE k < 100;

Несколько кортежей, конфликтующих по одному ключу

Ограничения

В настоящее время функция ON CONFLICT DO UPDATE DuckDB ограничена обеспечением соблюдения ограничений между зафиксированными и вновь вставленными (локальными для транзакции) данными. Другими словами, наличие нескольких кортежей, конфликтующих по одному ключу, не поддерживается. Если в новых данных есть дублирующиеся строки, будет выдано сообщение об ошибке или может возникнуть непредсказуемое поведение. Это также включает конфликты **только** внутри новых данных.

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    VALUES (1, 84), (1, 168)
    ON CONFLICT DO UPDATE SET j = j + EXCLUDED.j;

Возвращается следующее сообщение.

Error: Invalid Input Error: ON CONFLICT DO UPDATE can not update the same row twice in the same command.
Ensure that no rows proposed for insertion within the same command have duplicate constrained values

Чтобы обойти это, обеспечьте уникальность, используя DISTINCT ON. Например:

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
    VALUES (1, 42);
INSERT INTO tbl
    SELECT DISTINCT ON(i) i, j FROM VALUES (1, 84), (1, 168) AS t (i, j)
    ON CONFLICT DO UPDATE SET j = j + EXCLUDED.j;
SELECT * FROM tbl;

i	j
1	126

Оператор `RETURNING`

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

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

Например:

CREATE TABLE t1 (i INTEGER);
INSERT INTO t1
    SELECT 42
    RETURNING *;

i
42

Более сложный пример, который включает выражение в операторе RETURNING:

CREATE TABLE t2 (i INTEGER, j INTEGER);
INSERT INTO t2
    SELECT 2 AS i, 3 AS j
    RETURNING *, i * j AS i_times_j;

i	j	i_times_j
2	3	6

Следующий пример демонстрирует ситуацию, когда оператор RETURNING более полезен. Сначала создается таблица с столбцом первичного ключа. Затем создается последовательность, позволяющая инкрементировать этот первичный ключ при вставке новых строк. При вставке в таблицу мы ещё не знаем значения, сгенерированные последовательностью, поэтому полезно их возвращать. Для дополнительной информации см. CREATE SEQUENCE страницу.

CREATE TABLE t3 (i INTEGER PRIMARY KEY, j INTEGER);
CREATE SEQUENCE 't3_key';
INSERT INTO t3
    SELECT nextval('t3_key') AS i, 42 AS j
    UNION ALL
    SELECT nextval('t3_key') AS i, 43 AS j
    RETURNING *;

i	j
1	42
2	43