CODEWITHSUNDEEP
sqlpostgresqlupsert
Ritik Harchani
Ritik Harchani

Reputation: 99

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

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

Upvotes: 7

Views: 5041

Answers (3)

jian
jian

Reputation: 4877

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;

Upvotes: 1

kofemann
kofemann

Reputation: 4423

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

Upvotes: 4

Alex Yu
Alex Yu

Reputation: 3537

You need WHERE clause in ON CONFLICT.

INSERT INTO table_data 
VALUES (data) 
  ON CONFLICT (fields) 
  DO UPDATE SET  data=excluded.data 
  WHERE EXCLUDED.updated_mode='automatic'

Take a look at db fiddle: https://www.db-fiddle.com/f/qwRzRSFaJx4KDMYn2GEXTe/1

Upvotes: 8

Related Questions