PostgreSQL - Upsert query using ON CONFLICT clause


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



Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...