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;




PostgreSQL: Function and its uses

The function is the set of PostgreSQL statements that stored on the database server and can be invoked using the SQL interface. PostgreSQL ...