public inbox for [email protected]  

help / color / mirror / Atom feed

From: Karsten Hilbert <[email protected]>
To: pgsql-general <[email protected]>
Subject: unique constraint violation on multiple-rows update
Date: Sun, 19 Apr 2026 15:17:07 +0200
Message-ID: <[email protected]> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: unique constraint violation on multiple-rows update
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox