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(dot)klaver(at)aklaver(dot)com