Re: Update violating constraint
От | Naz Gassiep |
---|---|
Тема | Re: Update violating constraint |
Дата | |
Msg-id | 463966B8.7060908@mira.net обсуждение исходный текст |
Ответ на | Re: Update violating constraint (Michael Glaesemann <grzm@seespotcode.net>) |
Ответы |
Re: Update violating constraint
Re: Update violating constraint |
Список | pgsql-general |
Michael Glaesemann wrote: > > On May 2, 2007, at 23:01 , Naz Gassiep wrote: > >> I'm trying to do an update on a table that has a unique constraint >> on the field, I need to update the table by setting field = field+1 >> however if this does not perform the updates on the table in a proper >> order (from last to first) then the update will cause a violation of the >> index *during* the update even though the table would be consistent >> after the update completes. > > If field's values are all positive, I generally will do it in two steps: > > update foo > set field = -1 * (field + 1); > update foo > set field = -1 * field > where field < 0; > > Another way to do it is to add and then remove a large offset: > > update foo > set field = 100000 * (field + 1); > update foo > set field = field - 100000 > where field > 100000; Yes, in fact I actually use option one already in the handling of sql trees, so I'm annoyed with myself for not figuring that out. I don't know why you'd ever use your second option ever, as it virtually guarantees problems at a random point in your DB's growth. Thanks muchly for that!
В списке pgsql-general по дате отправления: