How to use IF ELSE with ON CONFLICT clause in postgresql?

user_12696706 0
Viewed 895

I have an airflow job upserting the columns of my table on daily basis via INSERT ON CONFLICT statement. The table contains a field updated_mode of type enum which is set to automatic when the row is inserted/updated by job or manual if's done manually. Now, I want my job to update rows only if updated_mode is set to automatic. How can I do that?

Basically, I want to do something like:

Insert into table (data) values (data) on conflict (fields) if updated_mode=automatic set data=excluded.data else do nothing
upsert postgresql sql
3 Answers
user_11682120

You should use the regular WHERE condition. The magic EXCLUDEED RECORD will contain existing confliting record. Something like that :

 Insert into table (data) values (data) 
 on conflict (fields) do update 
   set data=excluded.data 
 WHERE updated_mode=automatic 
   and fields = EXCLUDEED.fields

I assume that fields is the conflicting field and table name is data

user_13568830

https://www.postgresql.org/docs/release/14.0/

  • Allow column names in the WHERE clause of ON CONFLICT to be table-qualified (Tom Lane)

Now You can more easily reference excluded column names and the original table columns.
setup the table.

create table test101(id bigint primary key, field1 text, update_mode boolean);
insert into test101 values (1,'a', TRUE);
insert into test101 values (2 ,'b', false);

excluded refer to the part you want to insert.

--this one will insert.
insert into  test101(id, field1,update_mode) 
    values(1,'asdf', TRUE)
on conflict (id) 
do update set
field1 = excluded.field1
WHERE 
    excluded.update_mode= test101.update_mode;

--this will not insert
insert into  test101(id, field1,update_mode)
values(2,'asdf', TRUE)
on conflict (id) 
do update set
field1 = excluded.field1
WHERE 
excluded.update_mode= test101.update_mode;
user_156034770
Related