How to insert a row into another table during an on conflict-CodePudding

I want to insert in a ManyToMany relationship (between 'fiche' and quality) a line (same principle as a log) when an insertion fails because the key is duplicated.

example:

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

My error:

ERROR:  syntax error at or near "insert"

I'm in a loop doing mass inserts and I want to be able to keep track when I have a duplicate (detected by uniqueness).

CodePudding user response：

You may use a PL/pgSQL block to catch an unique_violation exception and handle it.

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;
$$;

The block may be shaped as a reusable function too.

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;
$$;

CodePudding user response：

Triggers are the most intuitive / flexible approach, but since your use case is only about keeping track of the duplicated entries in a separated table you can use a CTE (aka WITH clause) returning the xmax of the new record. If it is new, it will return 0. Then finally in the outer query you filter only the inserted records to insert in the "duplicates" table, e.g.

WITH j AS (
  INSERT INTO t VALUES (1,'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

Demo: db<>fiddle