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