Re: unique constraint violation on multiple-rows update
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
Import Notes
Reply to msg id not found: aeTV0z7oiFYtDMzu@hermes.hilbert.locReference msg id not found: aeTV0z7oiFYtDMzu@hermes.hilbert.loc
On Sun, Apr 19, 2026 at 6:44 AM Karsten Hilbert <Karsten.Hilbert@gmx.net>
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.
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
Karsten Hilbert <Karsten.Hilbert@gmx.net> 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
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