Search Postgresql Archives

Re: DELETING then INSERTING record with same PK in the same TRANSACTION

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


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 Facebook LinkedIn Twitter Instagram
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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux