Question 90245 in PostgreSQL Question posted in
The official documentation can be found here.

How to insert a row into another table during an on conflict – Postgresql

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).

Tags:

2

Answers


  1. 0

    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;
$$;
    Login or Signup to reply.
  2. 0

    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 records – new records always have a xmax = 0. Then finally in the outer query you filter only the inserted records to store in the "duplicates" table, e.g.

    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

    Demo: db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search