DELETING then INSERTING record with same PK in the same TRANSACTION

Поиск
Список
Период
Сортировка
От Andrew Hardy
Тема DELETING then INSERTING record with same PK in the same TRANSACTION
Дата
Msg-id CAKxhU8isUe6NUs7i7dJzdVeHHoK417Sv=z7jUoHZMHbezG_VkA@mail.gmail.com
обсуждение исходный текст
Ответы Re: DELETING then INSERTING record with same PK in the same TRANSACTION  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: DELETING then INSERTING record with same PK in the same TRANSACTION  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: DELETING then INSERTING record with same PK in the same TRANSACTION  (Brent Wood <Brent.Wood@niwa.co.nz>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Network Card Not Listening at Startup
Следующее
От: Brent Wood
Дата:
Сообщение: Re: DELETING then INSERTING record with same PK in the same TRANSACTION