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