Updating another table on conflict postgres

Question

I have a requirement to insert on one table (test01) and update on another table(result) whenever the conflict arises. I tried with below query:

insert into test01 as cst (col1,col2) 
select col1,col2 from (
select 1 col1,'test' col2) as excluded
on conflict (col1) do 
   update result as rst set conflictid = excluded.col1, updated_at = now() where rst.conflictid= excluded.col1 ;

but its returns "syntax error at or near result". Can anyone please help me with the right solution.

Possible duplicate of https://stackoverflow.com/questions/36359440/postgresql-insert-on-conflict-update-upsert-use-all-excluded-values — Roopesh, Aug 23 '21 at 13:03
@Roopesh Thanks for your reply. My requirement here is to update another table when the conflict arises. — kaviya .P, Aug 23 '21 at 14:03

score 1 · Answer 1 · answered Aug 23 '21 at 14:42

Basically, your approach is not feasible. The ON CONFLICT ... DO UPDATE clause applies only to the table into which the rows are inserted. See INSERT syntax in the documentation.

A solution requires a bit more work. You should create a trigger for table test01 to get the effect you want.

Example tables (slightly changed names of columns and table):

create table test01_conflicts(
    conflict_id int primary key, 
    updated_at timestamp);

create table test01(
    id int primary key, 
    str text);

When the table test01 is updated with the same id the trigger function inserts or updates a row in the conflict table. Note that in this case the function returns null so the update on test01 will not proceed.

create or replace function before_update_on_test01()
returns trigger language plpgsql as $$
begin
    if new.id = old.id then
        insert into test01_conflicts(conflict_id, updated_at)
        values (new.id, now())
        on conflict(conflict_id)
        do update set updated_at = now();
        return null;
    end if;
    return new;
end $$;

create trigger before_update_on_test01
before update on test01
for each row execute procedure before_update_on_test01();

The query. On conflict update test01 with the same id only:

insert into test01
select col1, col2 
from (
    select 1 as col1, 'test' as col2
    ) as source
on conflict (id) do 
    update set
    id = excluded.id;

This is the correct solution. I tested it and it works. I wonder if this workaround has any performance drawbacks or not. — Farzad Soltani, Oct 06 '22 at 10:21
@FarzadSoltani - Of course, massive inserts with frequent conflicts will be slower but the only additional cost is to update the auxiliary table. — klin, Oct 06 '22 at 10:41
I think if they added a `ON CONFLICT DO IF ..... THEN UPDATE ....` syntax it would eradicate the need for such complex logic handling. — Farzad Soltani, Oct 07 '22 at 10:10
You cannot use `IF` in this context. Only `DO NOTHING` or `DO UPDATE SET`. — klin, Oct 07 '22 at 10:42

Updating another table on conflict postgres

1 Answers1