My take on this...
Because both statements are in the transaction, the delete is not fully
actioned until the commit. So it still exists in the table when you try to
insert the record with the duplicate key.
Check if the error is generated during the transaction or at the commit stage,
run it without the commit, rollback instead to check this.
I don't see how you can do this within a transaction, someone else might?
Brent Wood
Principal Technician, Fisheries
NIWA
DDI: +64 (4) 3860529
________________________________
From: Andrew Hardy <andrew.ha...@sabstt.com>
Sent: Thursday, February 10, 2022 07:11
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: DELETING then INSERTING record with same PK in the same TRANSACTION
Hi,
When I:
Begin a transaction
DELETE from <table> where id (PK) = <somevalue>
INSERT INTO <table> VALUES (<values - same PK>)
...
...
...
COMMIT
I get
insert into "itinerary" ("dk", "locator") values ($1, $2) - duplicate key value
violates unique constraint "itinerary_pkey"
Do I need some particular kind of settings on my transaction to be able to
delete and insert afresh in the same transaction?
In case it is relevant - the first delete will lead to cascaded deletes on
children.
Alternatively I wonder if I change the PK column value to
"<somevalue>-FORDELETION" will I be free to insert under the same original PK
value in the same transaction, then delete the FORDELETE item just before
committing or will I hit the same issue?
Thanks,
Andrew
[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png]
<https://www.niwa.co.nz>
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz>
Facebook<https://www.facebook.com/nzniwa>
LinkedIn<https://www.linkedin.com/company/niwa>
Twitter<https://twitter.com/niwa_nz>
Instagram<https://www.instagram.com/niwa_science>
To ensure compliance with legal requirements and to maintain cyber security
standards, NIWA's IT systems are subject to ongoing monitoring, activity
logging and auditing. This monitoring and auditing service may be provided by
third parties. Such third parties can access information transmitted to,
processed by and stored on NIWA's IT systems
