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

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