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