|
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.hardy@xxxxxxxxxx>
Sent: Thursday, February 10, 2022 07:11 To: pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx> 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
|
