错误：重复的键值违反了postgres中的唯一约束 [英] ERROR: duplicate key value violates unique constraint in postgres

问题描述

这里slo_order_item_id是唯一约束

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);

我也尝试

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

但这些都给错了。

错误：重复的键值违反了唯一约束
unique_sale_order_slo_order_item_id详情：键
（slo_order_item_id）=（1027559930 ）已经存在。

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 ?

推荐答案

登台表中可能有重复的值。您可以通过以下方式进行检查：

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;

如果是这种情况，那么我建议修复登台表。但是，快速加载某些东西的方法是在上使用 distinct，而不是 distinct ：

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;

这篇关于错误：重复的键值违反了postgres中的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！