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

PostgreSQL - CrossTab Queries using tablefunc contrib

PostgreSQL statement uses the function crosstab to pivot the table records, pivot means I want to convert the rows to the column of particular column's value and want to the others column value respectively of converted rows.

PostgreSQL- CROSSTAB example

Suppose we have the following table

CREATE TABLE product (   id serial not null,   brand text,   category text,   qty integer ); -- Insert some records INSERT INTO product(brand,category,qty)   VALUES('Arrow','Cloths',3000); INSERT INTO product(brand,category,qty)   VALUES('Samsung','Mobile',4500); INSERT INTO product(brand,category,qty)   VALUES('iPad','Tablet',2000); INSERT INTO product(brand,category,qty)   VALUES('Prestige','Kitchen',200);

Now, select query

SELECT * FROM product;

Result:

id	brand	category	qty
1	"Arrow"	"Cloths"	3000
2	"Samsung"	"Mobile"	4500
3	"iPad"	"Tablet"	2000
4	"Prestige"	"Kitchen"	200

But expected result as below

Result:

brand	Cloths	mobile	Tablet	kitchen
"Arrow"	3000
"iPad"			2000
"Prestige"				200
"Samsung"		4500

Using CROSSTAB we can achieve the pivot goal, se the following query

SELECT * FROM   crosstab(    'SELECT brand, category, qty     FROM   product     ORDER  BY 1,2',                 $$VALUES ('Cloths'::text), ('Mobile'),('Tablet'),('Kitchen')$$    ) AS ct (brand text, Cloths int, Mobile int, Tablet int, Kitchen int);

 Result:

brand	Cloths	Mobile	Tablet	kitchen
"Arrow"	3000
"iPad"			2000
"Prestige"				200
"Samsung"		4500

Note: If you are getting the following error:

ERROR:  function crosstab(unknown, unknown) does not exist

LINE 2: FROM   crosstab(

               ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SQL state: 42883

Character: 17

The above error will remove executing the following PostgreSQL statement

CREATE EXTENSION IF NOT EXISTS tablefunc;

PostgreSQL- Select first row in each GROUP BY group

I want to select the first row of each group grouped by the PostgreSQL keyword GROUP BY. Suppose I am working on purchase reporting and I want to select the customer who has a max value of the price.

I have the following table

CREATE TABLE orders (           id serial not null,           name text,           itemQty integer,           price decimal ); --Insert some records insert into orders(name,itemqty, price)   values('Ashish',2,200); insert into orders(name,itemqty, price)   values('Naina',8,300);  insert into orders(name,itemqty, price)   values('Mamta',10,2000); insert into orders(name,itemqty, price)   values('Ashish',4,500);  insert into orders(name,itemqty, price)   values('Naina',4,100);  insert into orders(name,itemqty, price)   values('Mamta',6,2500); insert into orders(name,itemqty, price)   values('Mamta',5,1500);

Simple select query

SELECT * FROM orders

Result:

But I want to result as below:

There are several ways to resolve this problem, please the below PostgreSQL statements/t-sql queries.

1- Using common table expression:

WITH cte AS (     SELECT p.id,            p.name,            p.itemqty,                              p.price,            ROW_NUMBER() OVER(PARTITION BY p.name                                  ORDER BY p.price DESC) AS rnk       FROM orders p) SELECT o.*   FROM cte o  WHERE o.rnk = 1;

Result:

 2- Using DISTINCT ON keyword

SELECT DISTINCT ON (name)        id, name, itemqty, price FROM   orders ORDER  BY name, price DESC, id;

Result:

3- Using row_number() in subquery

SELECT id, name, itemqty, price FROM   (    SELECT id,name, itemqty, price         , row_number() OVER(PARTITION BY name ORDER BY price DESC) AS rn    FROM   orders    ) tmp WHERE  rn = 1;

 Result:

4- Using array_agg() with ORDER BY

SELECT (array_agg(id ORDER BY price DESC))[1] AS id      , name      , max(price) AS price FROM   orders GROUP  BY name;