Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wESRK-004263-2p for pgsql-general@arkaria.postgresql.org; Sun, 19 Apr 2026 13:43:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wESRI-00FIH7-2H for pgsql-general@arkaria.postgresql.org; Sun, 19 Apr 2026 13:43:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wESRI-00FIGz-18 for pgsql-general@lists.postgresql.org; Sun, 19 Apr 2026 13:43:56 +0000 Received: from mout.gmx.net ([212.227.15.19]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1wESRG-00000001ym0-0k8z for pgsql-general@postgresql.org; Sun, 19 Apr 2026 13:43:56 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1776606233; x=1777211033; i=karsten.hilbert@gmx.net; bh=01WoVY6vh6ZpCFXrjdw52hxaMyEEsb+ESoxedPiwTJQ=; h=X-UI-Sender-Class:Date:From:To:Subject:Message-ID:References: MIME-Version:Content-Type:In-Reply-To:Content-Transfer-Encoding: cc:content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=enD2Ne6qtanILyTM81pn2D0ZcfI83S4l8Wox77BPst6rl0rHMMdtezB9bQor5GbK 4gTjbIDTi98h5CO5o62ZKRrMIspVMSSW6wOm+u7xdxWm1uAKVyy+zPLxXtbYKJ8YX fDUmnmgImt+lopY79wRDkr6aRZ9msY8Cdoj7glYpKiMvQ6zw11jNMW7KCHyBCtFKZ ktQ2Qjtx9ZXW3Q4NzuX5zMNTVMq959MTjwOqXF8N6TXQH4pJOGRWfTxyZbvDHatki YHiztFNAq+cEGC+E/TyQCC2gclMND7K7ylYJXYar1O2JE/48k3w+yuPSf4asyO9SR 6/107I48DId67rHilg== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from client.hidden.invalid by mail.gmx.net (mrgmx005 [212.227.17.190]) with ESMTPSA (Nemesis) id 1N1fn0-1vGsk42Mq8-00upo2; Sun, 19 Apr 2026 15:43:52 +0200 Received: from ncq by hermes with local (Exim 4.98.2) (envelope-from ) id 1wESRD-000000006gz-45I4; Sun, 19 Apr 2026 15:43:51 +0200 Date: Sun, 19 Apr 2026 15:43:51 +0200 From: Karsten Hilbert To: pgsql-general@lists.postgresql.org, pgsql-general Subject: Re: unique constraint violation on multiple-rows update Message-ID: Mail-Followup-To: pgsql-general@lists.postgresql.org, pgsql-general References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) Sender: X-Provags-ID: V03:K1:O+Y4C8oLUtW/aNeoaIr3pEgXkbtYq4g1WVWHbbuCI7Hfed3e+/l hFW6e9BrnO6wg3YgrFHxSSBtin9kxiQDxzRVa0t57UOX8IPKuy+G9c2VAvEXNI+ahhhtIx2 0z+vmaoDTGyTxxm4yjDWd7yB3D/Q+OSE2jc6C7Qrhz059RC7mLRfUXht/YeO2Npq/g/u63R 6mfQKrheIQgiZkU8Yomzg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:yOo/d7R05dw=;gS9WeEHkdW+O1lBzy3zTM1NdRds jGAUqmRxct1LL+qA2969gs0FDNuUFhUIw//4Xc9ZmbXqxrlMqTTvhEIH0VxMg0aTxirivjk+U K4W6u/AXEbZy418jX7XSeulr4F3w8/BcggSFLVp4yAdB8URKQRwUwXY0f44Xtei3peWse8nZ3 bt2S8VUDUg3PeiOBFbCBERoY9FAaZgpWeDax9+MGqsT1HMosX8TEZiL2R0rJX2f/zXf+p5VVS pdtqzoMaaFRXBLp7T6K81dUwBZZIghAFNSN/XxVx2oWyX2Fo71k5sgYowPH6+WZLiTT51Hzss 6D83/0Y8h8GRHAjckNWtXpP5DcoainkKRkputwyIAfbBrL5FzRHyin4gJhSFws6NTji6ZB2ev cL1xk5tBOFoSn1XsJ/F0OVSqKnPg3GWqIvR7sTwVb3PJyzvbHmRQJqG2m6aRXlUqLlU4+fOnL k0zstuPfNbMpfd/FqNTQ7DOTUKPbVsZc6sS7CR0DiqRiK/xi++GXorKeMC7s+5g8WKNjInT3d 1auXWfDCayfGN2yEEX8PeJNEaKIuVDgI4B+ORaAMjXdm3ZB2W9MaS2dDR3xi0HQBj4xwQElLo U1oF8yGNay5h00x/dqcqYrbQqPTxAMlY6sBvXvZKwl5M5tRZ5Z8HFKUHZ36QsQI2Pq7DEGarW 2n7v5tzGUsqU4F/cp9PFl7XELIZW77PgMTO10xmVyq6uj7H2qg2Ajgn+rwXHlZ44WZHukp45w GUQK5LuBW6ip3otrYkajG4YNBPb5gbdJco0WaKNC72ZBmLjvzCG0NhEdP1U9jD5+h0h3UFMEU KTn2ENY5Efi3dKW25oWAu6Fel3tqgNIUCOtv/OjHrWT6txqRuUFHMyT88X73E+fM+ozU6/NjZ Z5716Tew1pAphR7zpZh/H6/FAmaOR4pHRh9HWKNP4vRL9pcnSuDCHq/7fS1lINWTivKi7hnzF 745MgHMj6DAbMiIwFZKaCbnasyFBvJruTh1QeWIP0BFuCZCdZK6PLXDP6b1FpaCs3xUhehinw ygFE+XfPNWKm4A/5Agxgr92W2CHEEz/CZQfGInhLXG7hIM5xMtpKn3GD6CfbDv3d0G8jhLZIu a3if2IiY6KWO9fMxWb96aGs0CwUDRcnvAg0fRfKaSmJ+t3l1K7PZhqZHLZ0hFChaLhFMNpjw/ yxOmvjU9oBsJ9FlzG9fCpo48OTLOkjbd8szW0YBszuFh68jiKmjPwM66OVTlV6Q+eZbcxbNwo cNy20KMRseo9dEfKxXtD2MSv/L+ifjZ7sKIUCJawrHYrNmabkcuzXj14pIAonWbbyEpmEqRT7 z4Thcqbv6kWJn4jz1LRHgsRGiSqH5hpN1fXwz6b59s9VQM1bxWgvJ2OaGnGVFT8P6hnihrPNN z6otEQLOU2AlNBHo6WllhccJ+ugsj76PasiFeTBzgLulz5zrcEeT33Go7eEZ4OxhZHy2LKyLS UAKG0fQtNQDTbqRnNVuWrfPPVWMgkoEM3k/YalnQo2yFmSigI9uVTev2HjZm/ZJgiMYdTlXj4 j/lFp4H74XJgcT3IRRCHjXoi5vDNJZ0VoXjAFLjkJnMofOXJAhnOC6ko3eBtRueKzd8mvoQy3 HGkGhleTJQng1imw4r2lklC7shWODKuLRmZ7h9RkINa2oV7rsDEj4aYUz3Qmxr4W7aDD8v7Uj ljWSx1xLeZsqdBMToxWv4VeWvyDfWUUeL+bJfzJsuhmlhlR8ZOb+sj7moXMhjT6zM9hZNdUzn /uHGj481ke0TdRapDutU8w0JcEw49mD3avHdeAX9oN6xYvTU4ukvLv9iKw5KpM9gPF5YS+8RB Qd/oKP5vrda/FcHry0eRgpKBRbLyf8N6IdQo+V/znTdbHHCFmJaGonkJwjFuei6/zGTNaK0Cr UkiYrYbccPbCyBJ1/BoJstSnW7KMO/1+WhbVUrW3OddUjksReifvcN6C1VphXxEwBFAdOhjHZ cm3mgU73vs6D6MK9mcD5xfF6+Jle0Lz2HF96d+6qOFKlFY5oZQJRGnYEeRst+u7of2hhYwka1 6lqXZXemN5Y61NGXppbp0U/STxWFBDXlKcGp+jstsvNqRy3RiMddwfBaD/xP+1avEqxnMGa1w D1+AW3axVtd3xO7hAl4QhLv3rYFZAtN1Vvg9t/11DGQrl2v8yiH1mxMLcyeDdFeNnt34wdImv 3bC6wkw6JTfu/+s0dJgXA4/oVY+M1hu3QTAIeQZaeFTdyizW9GFxcaGLpVYdDrsV3vfBAv5vn J3kJoFdtHjBgUtxVQFCthsYlAUqvW0FUiWSDJLoNKz8laL9hE5pvPqu6g8pAik5OMxVJAa+Dn P1icMWp1PDZTj1Fkq7EWPXvnJqk/d4gWxYTxhv04A/20mCRYymPKIPmWl7zL4OjVIM6Vq3qiv HRyt0lmwLPK5v6Nj65d0U0HHQkTY7WvSyvLwWtcIQrHTl7Yz8OUrVpeZsTaKhY8+F7ebmrp7x zarhY+VLFGp1A7nelIDPixcMnLNyAqpwGDgWUPTOYJew2N/5sjpaMeXGS9RVVlcTnb3vHx6ZY J/PW5utL3d9xmxQm6df2pHPqkXAMzJyMT5/melNRnRMKs/p45sG7GLU44VVKZVK+lOrCe8hLd 4wIwKGe8nlqNLMZbN6/3lP6+5we07lDLUj2Aw3OzqW6nfRKUGywvi8uIbNvUL6ZF+iTwhD1BL GvGBmq+GgeS+f1wnhkoJ/57HyqAkxLHu8GvkgF63L20kJXe+B5VzDBT0SfmnXkSDJWaJbuvW7 Nj/crlQuRIufBtx3liCUu1ZYNgJSq9gjnK/TjD3lAIqzHKgottalDN4zyuQwjMtxNZtxZoAyX f2RpQ9XeL5A05Re/Tt6QboSMmHucZDlUkcxgS9O1Xo/oiN5z7JYYk07e7MpLUEGPlxeZzE1Rg hLHeuKyNCSkiq9X4aMx4qGb7DQ9laZvsQ90r1q1Jg1qTkd60Blp3rOK/x8XvSpM4uXA/Y4iA/ aSFduSoau8Yzg0nhYc/zkGBKEo9+j+y1XXPoVlL/HL0jCOHqgXKAgrH0kTMUZBqOMFrbBFbgt VNQyY3J/KxhHGombasbxiRQGqip0q+dvsRXsO+QMYS+ceFbzep2t3opcd8FIF5KXjDJBORunC OT4lckPOumy+aJx5CTbQGv0ECRwH9JNepQfLQ8R93fmeJGB04zweLz5xDvmExPRo+OrLFEQwh tFnxyc6QidhIMzsRuARMVLRuJqEW3Y6O8PtN95CcU5YjB8aFA4+E9JEZoDvOLBeGn+h8QBMGS io4vi9pCeF30ngYR9TX8BuQjhAmZvgh07V5yTDkQL+XNU5rygT/NWPQ/BCiPRoxVKYcDn1IiV 9xzHbLifGNJcpS1KzJrUXW7YH7rlNM33x4Xm84TDn5n1pKkOrcyIZZ/Ph9W5qp3/8ZencGq9X 04n/lrdGutkqWRRTvZg391Ykqu/IpvpVZ9CcrozV/C8847fuc47p+ugjQd5OHueFL5goxGzg/ KOLgkrxsHP8vGf/FwTB+k+l21GaHkq2Fonv8vVLJYTOd5o5IrdqZxnoBXO4sFCrljAtAyMgOR IXyb1WNQrMwm+ecxFGDaJNcdakYR1Eppcf1+N081ohjxzltls0XjI5rRdYfejpRy0hFLrW3By Q4JFrZkbpTgDtE4neL8/kE5CYnskP32FxcJs7b4euUpIsUrc9eejvHWi56zrhZME8AZxtwwxY G8Gy4r1RI0+B4pnaBK+jfRoto4uyU1P59vOPgnAubh4xgLkGID1zBVUvNOU/ZdeGLQqHCOxFX S3MnGgcEFGqxIjFkh+kZSFGgpbtEqeDLDHiaEjbFCpnCNhtrm8zcWQtDr6/mSEDoDRsbXFMjj 8rOUE6xwT0CUKzUAVfD+7hmLuPD6wmO83Fyr5VR5lE0P0Io8PGpz2aLBrMHkG6OeFg05hu9mF wz3ZoWXURcbbgE5VI9rDSJkMa8qtlZwsBkVP9OeGLty0HhC7PZvI/FUjQeaxMlomlMe5sLpOh JI6eR4bdaA3qDe2wsJan+FuD80fmKji9Pc7HcsvzoBA8XNxJCXrPCcQdYxUqCP8G+JWE6PzZ8 GODNrJ9FM4kFOo9Utzp8aYk96u/rf5cV5zqL+BSiTH3mZUIvhNIb/YrLRzKIuvWAi1lZ19Fzw jrup4X1WsHYXCNt8ijrDOwBRCBIgvdcx9nDk6Jyl6laaVbNxHN/wJYUwxo2C4A5DLjOwsgCKU HuIAw20kiZywK09jMp37nBBdEo+FMTYiNgPMI1L8IJsXvT9c8yQ76zaiUL1Wi8Nyx58o5mNOF ZrvLmaHsAKS4SnhbonrGwjdRB0c1IeZhfXgUJYFuRIoyBiCLz+4IJOqZZapvm2OBbdgBRhKH0 G2fon9p65k/UdtbZMC9S7PwrQEEhouYqKx0Qteb88OD8rjgi7A9s2P/Ybw4fN/bJ/95ly0D1h eXl6um0R6+yneTyBw6sOV3Mw5yqXrCePkBSvAUnS9lI4YwwyAmHKTeq+7ufECpWRT+uvKEa04 12ekqf/CxjjfJWn0/TYhHPJdrz2EJp6o+6Nm4S4f0n+y4JfP8/jvdSQpDDdfLtnXoMsLm8BUA Ktd0Y3+DfnXp0bg35PuzfoQbASO7IYF2meK3cb0Vn9YOJNUODTK/w1dRm47+/3tjTf+jXFoT2 IyX0/x001Kw8Sg0Hv3ttDlMbNP3g1wyUCR+2X4Ow4taSW96iF4+41i+Brp5dmlOGsjpFcWvaT yNkxeJBQz79srQg9pUbJJEc2pHTDjPU0HpWePyDxGvGPWpgBfwhE1ZwINe9+v0nEdxSqbXUDE AFzLF0Ca2ZueROMlzwEr/kdLy06PT5HIl8W9O8zNJlX95paIhv7z0zWGhA1/TId8hvqTL+PEF gthgELpSD7IqoPWHHEdqRBT2xQCcMAIamGEa3ZByE055VD5ehtKS44m0D1aQeCHPLA90wuRy0 /8wZKa8L5eELjlxoflFzPU017faNLaO7XzydSOdpkDzESf74ok02ZB8CM+eGf8xSl1XAQxgFR fsfto9PV0O/VW8Ym1UZ8uJf5N5jhw7gxtgBnWMrxmAT0Kk9YZzsXvraRBUKxuuzcTE5WDIbOG 3Y/8e0XfwNFwTnJiKOe6pUIiq1ezou8Ia0Ep+gsONHbpPXuvBUwsvvQ9kWcQwpKd0XYrUhvQP ZGlJVdWPG1ltsf1x4pJWGHXOtJiDvbYG9qrz9un/K0nYmhpUwNMtHlWZXwfvGWJLT4C/Kl1Oz Mu2+iEUqDtAqAB3h7BiCTX+PG+1Zf1N+z7AFT1I4GcuoDDlBbILzn5462XwwSPWdF19gQt3Yp 2EBOJ0QUBLs/tpqF8IDrmFCMtUMTVORo425YI5AAw6cmbtksgKwN4f35ql5f8aM8c/ARQiWNj 3M3CiykJXzTMIoyLESm5SN907jLYoPV81yb+XJ4T7/cupLngpxg1SvdT+/patZVVoA9q8ajuq 6egVoOlN5yNYfjS070fGomRA== Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 >=3D _target_position > ORDER BY > list_position DESC > ) > UPDATE clin.export_item SET > list_position =3D cte.list_position + 1 > FROM cte > WHERE > clin.export_item.pk =3D cte.pk; >=20 > 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 =3D list_position + 1 FROM cte WHERE clin.export_item.pk =3D cte.pk; (note the lack of "cte." on the "list_position + 1") Karsten =2D-=20 GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B