Re: DELETING then INSERTING record with same PK in the same TRANSACTION - Mailing list pgsql-general

From Adrian Klaver
Subject Re: DELETING then INSERTING record with same PK in the same TRANSACTION
Date
Msg-id 2d136d22-e82d-c691-775b-c12aa29b079c@aklaver.com
Whole thread Raw
In response to Re: DELETING then INSERTING record with same PK in the same TRANSACTION  (Brent Wood <Brent.Wood@niwa.co.nz>)
List pgsql-general
On 2/9/22 10:34, Brent Wood wrote:
> 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.

No:

\d animals
                       Table "public.animals"
  Column |          Type          | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
  id     | integer                |           | not null |
  cond   | character varying(200) |           | not null |
  animal | character varying(200) |           | not null |
Indexes:
     "animals_pkey" PRIMARY KEY, btree (id)


delete from animals where id = 1;
DELETE 1

insert into animals values (1, 'great', 'opossum');
INSERT 0 1

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com



pgsql-general by date:

Previous
From: Andrew Hardy
Date:
Subject: Re: DELETING then INSERTING record with same PK in the same TRANSACTION
Next
From: "David G. Johnston"
Date:
Subject: Re: DELETING then INSERTING record with same PK in the same TRANSACTION