BUG #14585: duplicate key error after update when insert multiline

From: com2rela(at)daehyunst(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14585: duplicate key error after update when insert multiline
Date: 2017-03-09 10:44:27
Message-ID: 20170309104427.1418.49950@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14585
Logged by: ByoungChan Kim
Email address: com2rela(at)daehyunst(dot)com
PostgreSQL version: 9.5.6
Operating system: CentOS 7.3.1611
Description:

grade_id is serial primary key,
but insert data without grade_id after update some data like as below...

----transaction start----
update mSalesGrade set take_order = case when grade_id=138 then
'20170309154329' end,dpt_cd = case when grade_id=138 then '10310'
end,emp_nm = case when grade_id=138 then 'USERNAME' end,cst_code = case
when grade_id=138 then '12773' end,sitm_code = case when grade_id=138 then
'ITEMCODE' end,itm_width = case when grade_id=138 then '260' end,itm_length
= case when grade_id=138 then '200' end,lot_no = case when grade_id=138
then 'ITEMNumber' end,out_date = case when grade_id=138 then '20170309'
end,remark1 = case when grade_id=138 then '17.03.08' end,take_sales = case
when grade_id=138 then '00000000000000' end,take_cst = case when
grade_id=138 then '00000000000000' end,regist_server = case when
grade_id=138 then '00000000000000' end,remark2 = case when grade_id=138
then '' end,ord_qty = case when grade_id=138 then 4 end,update_dt = case
when grade_id=138 then current_timestamp end where grade_id in(138)
insert into
mSalesGrade(insert_emp,insert_dt,take_order,dpt_cd,emp_nm,cst_code,sitm_code,itm_width,itm_length,lot_no,out_date,remark1,take_sales,take_cst,regist_server,remark2,ord_qty,update_dt)values('USERCode',current_timestamp,'20170309184643','10303','USERNAME','10592','ITEMNumber','165','400','17224F13-6','20170309','','00000000000000','00000000000000','00000000000000','',6,current_timestamp)
---trnsaction end-----

error: duplicate key value violates unique constraint
detail: 'Key (grade_id)=(186) already exists.'

How can I solve it? After that error, I'm not using transaction at that
case(1.use serial 2.insert after update)

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Meskes 2017-03-09 13:24:29 Re: BUG #14582: ecpg crashes on SQL input
Previous Message Stepan Yankevych 2017-03-09 09:31:59 Re: BUG #14581: invalid cache ID: 41 CONTEXT: parallel worker