Как вставить строку в другую таблицу во время конфликта

Я хочу вставить в отношение ManyToMany (между «картой» и качеством) строку (по тому же принципу, что и в журнале), когда вставка не удалась из-за дублирования ключа.

пример:

insert into fiche (content) values ('toto')
 on conflict  (content) do
insert into fiche_qualites (poi_id, qualites_id) values (idPoi, 'Code error');

Моя ошибка:

ERROR:  syntax error at or near "insert"

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

Лучше использовать триггер. Я не думаю, что postgresql поддерживает вставку для on conlfictpostgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

— 07.01.2023 00:46

@JorgeCampos, однако, можно сделать CTE, который возвращает xmax вставленной записи. Если он возвращает 0, значит, запись новая :)

— 07.01.2023 14:11

Действительно, триггер может удовлетворить мои потребности, но, поскольку это массовое обновление и довольно длинный script.sql (с точки зрения строк), он кажется мне более удобным для сопровождения, и он также отвечает моим потребностям. большое спасибо !

— 07.01.2023 14:43

sql postgresql upsert

07.01.2023 00:41

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете использовать блок PL/pgSQL, чтобы поймать unique_violationисключение и обработать его.

do $$
begin
  insert into fiche (content) values ('toto');
exception 
  when unique_violation then
    insert into fiche_qualites (poi_id, qualites_id) values (idPoi, 'Code error');
end;
$$;

Блок также может иметь форму многократно используемой функции.

create or replace function insert_or_log(arg_content text)
returns void language plpgsql as
$$
begin
  insert into fiche (content) values (arg_content);
exception 
  when unique_violation then
    insert into fiche_qualites (poi_id, qualites_id)
    values ((select idPoi from fiche where content = arg_content), 'Code error');
end;
$$;

Как повторно использовать блог?

— 07.01.2023 15:07

Ответ обновлен с помощью функции. Добавлен скалярный подзапрос для извлечения idPoi конфликтующей fiche записи.

— 07.01.2023 16:12

07.01.2023 11:15

Триггеры являются наиболее интуитивным и гибким подходом, но, поскольку ваш вариант использования заключается только в отслеживании повторяющихся записей в отдельной таблице, вы можете использовать CTE (также известное как предложение WITH), возвращающее xmax новые записи - новые записи всегда имеют xmax = 0. Затем, наконец, во внешнем запросе вы фильтруете только вставленные записи для хранения в таблице «дубликатов», например

WITH j AS (
  INSERT INTO t VALUES (42,'foo')
  ON CONFLICT (id) 
  DO UPDATE SET txt = EXCLUDED.txt
  RETURNING xmax=0 AS inserted,*
)
INSERT INTO t_duplicates 
SELECT id,txt FROM j 
WHERE NOT inserted -- see boolean expression in the RETURNING clause

Демо: db<>рабочий пример

с другой стороны, если я добавлю второй раз (1, 'foo'), у меня возникнет ошибка: ОШИБКА: ON CONFLICT Команда DO UPDATE не может повлиять на строку во второй раз. ограниченные значения.

— 07.01.2023 15:01

@ Camel4488 Camel4488 из вашего вопроса я не понял, что у вас есть несколько вставок в одном выражении. Я думал, что это была только одна запись на вставку. Но вы правы, если это так, PL/pgSQL — это способ решить эту проблему (триггеры, функции, анонимные блоки кода и т. д.) ура!

— 07.01.2023 22:20

Извините, если оговорился, я использую переводчик. Действительно, у меня может быть 2 или более намеков на нарушение. Спасибо

— 08.01.2023 08:51

07.01.2023 13:58