ERROR: duplicate key value violates unique constraint in postgres

Question

here slo_order_item_id is unique constraint

 INSERT INTO shb.sale_order  
              ( 
                          slo_order_item_id, 
                          slo_order_id, 
                          slo_channel, 
                          slo_status, 
                          slo_channel_status, 
                          slo_order_date, 
                          slo_dispatch_by_date, 
                          slo_sku, 
                          slo_quantity, 

                          slo_selling_price, 
                          slo_shipping_charge, 

                          slo_vendor_id 
              ) 
  SELECT Distinct vss_order_item_id, 
         vss_order_id, 
         vss_channel_name, 
         vss_sale_order_item_status, 
         vss_sale_order_item_status, 
         case when is_date(vss_order_date) then vss_order_date::date else null end,
         case when is_date(vss_dispatch_date) then vss_dispatch_date::date else null end,
         vss_sku, 
         1, 

         vss_selling_price, 
         vss_shipping_charge, 

         vss_vendor_id 
  FROM   imp.vendor_sale_staging  udt
  WHERE not exists (select 1 from shb.sale_order where  slo_order_item_id = udt.vss_order_item_id);

and I also try

WHERE vss_order_item_id not in (select slo_order_item_id from shb.sale_order);

but these both are giving error.

ERROR: duplicate key value violates unique constraint "unique_sale_order_slo_order_item_id" DETAIL: Key (slo_order_item_id)=(1027559930) already exists.

why this is giving error ? which one where condition work fast and why ?

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-02-24T12:31:57

You may have duplicate values in the staging table. You can check by doing:

select vss_order_item_id, count(*)
from imp.vendor_sale_staging
group by vss_order_item_id
having count(*) > 1;

If this is the case, then I would suggest fixing the staging table. But, a fast hack to load something is to use distinct on rather than distinct:

SELECT Distinct on (vss_order_item_id) . . .
FROM imp.vendor_sale_staging udt
WHERE not exists (select 1 from shb.sale_order where  slo_order_item_id = udt.vss_order_item_id)
ORDER BY vss_order_item_id;

ERROR: duplicate key value violates unique constraint in postgres

1 Answers