public inbox for [email protected]
help / color / mirror / Atom feedunique constraint violation on multiple-rows update
6+ messages / 3 participants
[nested] [flat]
* unique constraint violation on multiple-rows update
@ 2026-04-19 13:17 Karsten Hilbert <[email protected]>
2026-04-19 13:43 ` Re: unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Karsten Hilbert @ 2026-04-19 13:17 UTC (permalink / raw)
To: pgsql-general
Dear all,
I am trying to update (business logic list) position
information of rows in a table. There is a unique constraint
on those positions. The relative order of rows needs to be
preserved but list positions need not be gapless.
The idea was to move out of the way any existing conflicting
row(s) by incrementing the list position. I tried to use a
CTE that returns rows in DESCending order and use that to
drive an UPDATE, like so:
WITH cte AS (
SELECT pk, list_position
FROM clin.export_item
WHERE
list_position >= _target_position
ORDER BY
list_position DESC
)
UPDATE clin.export_item SET
list_position = cte.list_position + 1
FROM cte
WHERE
clin.export_item.pk = cte.pk;
Running that does violate the (non-deferred) UNIQUE
constraint on the table column, however.
The Dept of Second Thoughts tells me that that UPDATE does
not care or even know about the CTE order and just updates
rows in whichever order it sees fit.
Is there a correct or better SQL idiom to use for the use case ?
This does run as part of a plpgsl function so I can rewrite
as a loop but I wonder whether I should be able achieve
the objective with a single UPDATE.
Many thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint violation on multiple-rows update
2026-04-19 13:17 unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
@ 2026-04-19 13:43 ` Karsten Hilbert <[email protected]>
2026-04-19 13:54 ` Re: unique constraint violation on multiple-rows update David G. Johnston <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Karsten Hilbert @ 2026-04-19 13:43 UTC (permalink / raw)
To: [email protected]; pgsql-general
Am Sun, Apr 19, 2026 at 03:17:07PM +0200 schrieb Karsten Hilbert:
> WITH cte AS (
> SELECT pk, list_position
> FROM clin.export_item
> WHERE
> list_position >= _target_position
> ORDER BY
> list_position DESC
> )
> UPDATE clin.export_item SET
> list_position = cte.list_position + 1
> FROM cte
> WHERE
> clin.export_item.pk = cte.pk;
>
> Running that does violate the (non-deferred) UNIQUE
> constraint on the table column, however.
Wait, should that UPDATE have been:
UPDATE clin.export_item SET
list_position = list_position + 1
FROM cte
WHERE
clin.export_item.pk = cte.pk;
(note the lack of "cte." on the "list_position + 1")
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint violation on multiple-rows update
2026-04-19 13:17 unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
2026-04-19 13:43 ` Re: unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
@ 2026-04-19 13:54 ` David G. Johnston <[email protected]>
2026-04-19 14:09 ` Re: unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2026-04-19 13:54 UTC (permalink / raw)
To: [email protected]; pgsql-general
On Sun, Apr 19, 2026 at 6:44 AM Karsten Hilbert <[email protected]>
wrote:
> > Running that does violate the (non-deferred) UNIQUE
> > constraint on the table column, however.
>
You know about deferred constraints, you should use them here. This is one
of the key use cases motivating their existence.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint violation on multiple-rows update
2026-04-19 13:17 unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
2026-04-19 13:43 ` Re: unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
2026-04-19 13:54 ` Re: unique constraint violation on multiple-rows update David G. Johnston <[email protected]>
@ 2026-04-19 14:09 ` Karsten Hilbert <[email protected]>
2026-04-19 15:17 ` Re: unique constraint violation on multiple-rows update Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Karsten Hilbert @ 2026-04-19 14:09 UTC (permalink / raw)
To: [email protected]; pgsql-general
Am Sun, Apr 19, 2026 at 06:54:02AM -0700 schrieb David G. Johnston:
> > > Running that does violate the (non-deferred) UNIQUE
> > > constraint on the table column, however.
>
> You know about deferred constraints, you should use them here. This is one
> of the key use cases motivating their existence.
Indeed. I was of the mistaken impression that UNIQUE
constraints were not deferrable ...
:-)
Works, thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint violation on multiple-rows update
2026-04-19 13:17 unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
2026-04-19 13:43 ` Re: unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
2026-04-19 13:54 ` Re: unique constraint violation on multiple-rows update David G. Johnston <[email protected]>
2026-04-19 14:09 ` Re: unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
@ 2026-04-19 15:17 ` Tom Lane <[email protected]>
2026-04-19 21:03 ` Re: unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2026-04-19 15:17 UTC (permalink / raw)
To: Karsten Hilbert <[email protected]>; +Cc: [email protected]; pgsql-general
Karsten Hilbert <[email protected]> writes:
> Am Sun, Apr 19, 2026 at 06:54:02AM -0700 schrieb David G. Johnston:
>> You know about deferred constraints, you should use them here. This is one
>> of the key use cases motivating their existence.
> Indeed. I was of the mistaken impression that UNIQUE
> constraints were not deferrable ...
Once upon a time they were not, but we fixed that years ago.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint violation on multiple-rows update
2026-04-19 13:17 unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
2026-04-19 13:43 ` Re: unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
2026-04-19 13:54 ` Re: unique constraint violation on multiple-rows update David G. Johnston <[email protected]>
2026-04-19 14:09 ` Re: unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
2026-04-19 15:17 ` Re: unique constraint violation on multiple-rows update Tom Lane <[email protected]>
@ 2026-04-19 21:03 ` Karsten Hilbert <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Karsten Hilbert @ 2026-04-19 21:03 UTC (permalink / raw)
To: pgsql-general
Am Sun, Apr 19, 2026 at 11:17:47AM -0400 schrieb Tom Lane:
> > Am Sun, Apr 19, 2026 at 06:54:02AM -0700 schrieb David G. Johnston:
> >> You know about deferred constraints, you should use them here. This is one
> >> of the key use cases motivating their existence.
>
> > Indeed. I was of the mistaken impression that UNIQUE
> > constraints were not deferrable ...
>
> Once upon a time they were not, but we fixed that years ago.
A late thanks, it came in handy today :-)
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-04-19 21:03 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-19 13:17 unique constraint violation on multiple-rows update Karsten Hilbert <[email protected]>
2026-04-19 13:43 ` Karsten Hilbert <[email protected]>
2026-04-19 13:54 ` David G. Johnston <[email protected]>
2026-04-19 14:09 ` Karsten Hilbert <[email protected]>
2026-04-19 15:17 ` Tom Lane <[email protected]>
2026-04-19 21:03 ` Karsten Hilbert <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox