I
want to insert data from a source that can contain duplicate data or data that
may exist into the table, so simple I want to add data that do not exist in the
table and update the table if data exist. In simple language, I wish to upsert
the data in a table from the data-source, data source means CSV, excel, another
table, etc. For example, suppose we have an Inventory table, and from the CSV
source, we need to insert data like prod_name, KPI, brand, qty, price, but it
maybe some products already exist in the Inventory table, and we must update
those data.
Syntax
INSERT INTO TableName(ColumnList) VALUES(ValueList)
ON CONFLICT target action;
|
PostgreSQL - ON CONFLICT clause example
ON
CONFLICT clause may help you to resolve the upsert issue.
create table inventory
(
id serial not null,
prod_name text,
kpi text,
qty integer,
price decimal,
unique(kpi)
);
--added some records to inventory
insert into inventory (prod_name, kpi, qty, price )
values('Prod1','prod1_kpi', 20, 100),
('Prod2','prod2_kpi', 30, 200);
|
select * from inventory order by id asc;
Result:
Now,
we will use the ON CONFLICT clause to upsert data into the Inventory table.
insert into inventory (prod_name, kpi, qty, price )
values('Prod3','prod3_kpi', 12, 122),
('Prod4','prod4_kpi', 28, 234),
('Prod1','prod1_kpi', 100, 110)--- It is existing
product
ON CONFLICT(kpi)
do update
set qty=excluded.qty, price =excluded.price;
|
select * from inventory order by id asc;
Result:
('Prod1','prod1_kpi', 100, 110)---
It is existing product so it updated in invetory as you see in above result
set.
('Prod3','prod3_kpi', 12,
122),('Prod4','prod4_kpi', 28, 234) --these data is not
duplicate so added in inventory table