postgres.email
❮

unique constraint violation on multiple-rows update

  • Jump to comment-1
    Karsten Hilbert<Karsten.Hilbert@gmx.net>
    Apr 19, 2026, 1:17 PM UTC
    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
    View in PostgreSQL Archives →
    • Jump to comment-1
      Karsten Hilbert<Karsten.Hilbert@gmx.net>
      Apr 19, 2026, 1:43 PM UTC
      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
      listposition >= target_position
      ORDER BY
      list_position DESC
      )
      UPDATE clin.export_item SET
      listposition = cte.listposition + 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
      View in PostgreSQL Archives →
      • Jump to comment-1
        David G. Johnston<david.g.johnston@gmail.com>
        Apr 19, 2026, 1:54 PM UTC
        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.
        View in PostgreSQL Archives →
        • Jump to comment-1
          Karsten Hilbert<Karsten.Hilbert@gmx.net>
          Apr 19, 2026, 2:09 PM UTC
          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
          View in PostgreSQL Archives →
          • Jump to comment-1
            Tom Lane<tgl@sss.pgh.pa.us>
            Apr 19, 2026, 3:18 PM UTC
            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
            View in PostgreSQL Archives →
            • Jump to comment-1
              Karsten Hilbert<Karsten.Hilbert@gmx.net>
              Apr 19, 2026, 9:03 PM UTC
              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
              View in PostgreSQL Archives →