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 1wES1S-0041VZ-1K for pgsql-general@arkaria.postgresql.org; Sun, 19 Apr 2026 13:17:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wES1R-00FCsM-2C for pgsql-general@arkaria.postgresql.org; Sun, 19 Apr 2026 13:17:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wES1R-00FCsE-0U for pgsql-general@lists.postgresql.org; Sun, 19 Apr 2026 13:17:13 +0000 Received: from mout.gmx.net ([212.227.15.19]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1wES1O-00000001m9V-1oSu for pgsql-general@postgresql.org; Sun, 19 Apr 2026 13:17:11 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1776604628; x=1777209428; i=karsten.hilbert@gmx.net; bh=rkLb76USqqDbSfO+u+FBjpTxcw43ymJ2QkhKkHU0S0Q=; h=X-UI-Sender-Class:Date:From:To:Subject:Message-ID:MIME-Version: Content-Type:Content-Transfer-Encoding:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=tZmSfOfuDIkUSjwLaBQBSKTdr51/wt/2BreJJ+wx5ertKWz4pZGyW30kyovviJgy xjOQ/GqxE5ialZK13CV2VV10Qm3XA72TJmLRbPbx19qgDRQ+S1mH/+SE7i+fCBh67 8VXJgjVFc7f62kIIxu+R+EeU3Di+3XR8vo99KNp1VCVIbAOq4KMJLDfxv41RggZU6 UiI3azql7SMlYzbE9J9yWppNgRUPF8GLQW6euWhnI4IKuOhDvRDgz66CMCm0CE9vL JztthyJ4TsBsqBrQnIY1S0b4poxU5ElvIuvACHf99fX7WRuzCwicUUv7C9vaAWhhP qk1jVxovyYh/2kHK5Q== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from client.hidden.invalid by mail.gmx.net (mrgmx004 [212.227.17.190]) with ESMTPSA (Nemesis) id 1Mf07E-1vmG4c0tBK-00qHL7 for ; Sun, 19 Apr 2026 15:17:08 +0200 Received: from ncq by hermes with local (Exim 4.98.2) (envelope-from ) id 1wES1L-000000003f8-26rl for pgsql-general@postgresql.org; Sun, 19 Apr 2026 15:17:07 +0200 Date: Sun, 19 Apr 2026 15:17:07 +0200 From: Karsten Hilbert To: pgsql-general Subject: unique constraint violation on multiple-rows update Message-ID: Mail-Followup-To: pgsql-general MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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:EJiDiafbAMqQxbja+FrE70X5jO4n7tEB+RyYU0/PJri6smvsG0B mqhb+bCaGaTJHCx29x8Cr/znxGRLjTfc50p0yO6KWSXLhx6gXDu9kPyl1f2VWkZPbBnYsa8 lrBr9Yt65itPunbBmBLj2gJUWlR8YYwpMvZ+ylpluS7YwS4fM062g/jGL8XSqLEPR+1AHgL HqMX1Nu1w38ej4FDJWE+w== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:/drmKDRw6uc=;4BZnpvTzwOcA+G4UniCvMDqo8Io lbHP3aLe3P9Ehvrz552URdLV/hN4ds/UZyChLDblLDygJPqlpVm7zM2/8tfXhKsK+wuqEXwEZ ljzXJjd3y7AVZJBIZgfuiMTOqLWFvLC/90f//G36vcZ5z8nFwuavsWLa2eVMLgSn4PqT6gmxK O/2JiQz/JFDPSrVl0a3kQdnEEtJXan/2Y7WHb3Az7gKJDReHmtmi4tYGTbocJwW++5gZHr0q0 s7DJ9iFTOui5qZ+1ivOoij8jMmii6FIlVlmclR8bdCKMhunykxJ28KIQcAEGZo0XQAdY/zyIR F2rmLtbWAqdtFdwS2QbHyagkY/9FsXQnfOQH0LE0fPkhBKpJIwKPuo7qI+q20N+mPQYYB0kKj G7c1WesedYYsVSj4T2+VWxLXo30VeOu8D0/GBpQCTaBMejEj+T+0bpRSvuU2GQ7gwZwlmEQli AD798/xJpBbmJUyABFh+CaW0HToCFisJUqNiM0MVTgFdjIy3Dsb9wXz2ZUsqCOMBny8TYuKC9 Sdy0+0Mf2FAz3INf1Qa1H+p7CSy92sxvtkNdPuu57nngO0W3GeSCiE1cuWbhqpr2UOAyK+Qvd x63NorUgkdHxM35mEaUB8tZ3hJXbTd728+2h7v5XVDZDZ7CeNqOD56d5B2f7Kfwb50bHWjlm8 d7TOfEkeQMGcAi04PF1biEOu0hBaP4wwKgr8ZYk4OClLJ6er5r+IHaSOSDjYpgAG8i2c6lXpU 17z/HoZJWTcSbJKcw5YSD6NAGr9vG0hVpQCb5Nu3611KH/YWmULVdjFnYpK+6Acv3mw8bG70B bOIjXjfLd2DKl5YLxU1TgkbW1gKzJ07J4d1VNjb5DMBhp8V/FgwjVjG2DruknhA7PtNFLFLJZ fNr787tIySPjzEpw4r14t/I5+36TSiPQeEBB4JxZkJQCVC4oVLm2obg2u/gAgJiPd2Nh7hZFZ dy/m7FqSMP5xJsC5SMvxpUuHxeoF70Q25+LxLrJrej1UDra456qGSrKx5OW1vDZLVcCdwIz0M G4hjqpAUv4pw6NCmQVfcfqbR+di8TW8M0LMsFFEIGGu9IlKPtGyt80JNrEbXD2/h2WF3XMOB1 jlGBYsOVj4k7Fc0QY2rEhUde52psH5AO0syY6ofbnkoUv4sl4wyuD2KAFMzPKJ9c2Pb/uTpIf GfunjuE0TgvIUV8bkaH7SZekrY4mPdJ2XtqPiNkYgfhMReXWBKwk6WiFyaaDnIbyTb9knCpSN pKN6GkJQxM7bozy+iuNXOpuBWOy0z10xf8cEYmiAzApq0cNZ0JPpWiQuJT6/NP4jn83VB7tLH HBK3qlx4p7a7I0vGeoX+55we7Kdft4vu6Dt/GLzeYhg027eoRGyPm93Mk1VJ17xIIxdaXhqCy Evgh7B/RgRfLPPc406x3ORIUuCrXFY+f/XNF1A2s3o3oijizrC+n9mTQy3bSZcD67zP2DvQSC 5X29ulR8bJd+RD+dhAcfw+rdXLwsOvSbJh9dYPUtNp2IkjJFm3T3IM+MxQfnje1X4TZLE5t/q yLp9lqOeCtX+VAb/GYtaBiDmgdI/cTL7VqfZhTYlhElj9+KPP114/4prokb/HzSqLz6qS4rvf a8KeGAd+GbC9Mg5Gu3TnYaLGiZYfjjEJH1DeQlIEwoyllB7Z/cyjkFC9QWSzjeYNJAtw3BDP4 RpKei0nAI0a2moZU0VeW01qEqVHI/hV88qN8IYRutIoLpneF0LsKPYHd/7pz6bIbmSXapYJkI rptHggXgjOHxuiQ9PWO4ueyRh3goAijOi5w4ERHXUtjOeyAIjOKj053ZhV1Xsv2VWEOEDnj9A 264eV8TlwayZAepO6cyDTpEKZFQpd/LR9nJD8n1tBusFCHiTSzv+Ob/p3x5cFauJnPRf9j8Tk dYP2ptEuWU5UvVzCOVzu/9vuKcW6+YzhM+j60UNAm3ZZLinofdKXIOKmTDUa+waqRNIT+kZ+V seMdTVju1jVk0gwOOMA49aKhNkVndOnvJjD28/LpqRoG/L4C2+DQQCv0AnBTrZHzdZ6P4BrBB bNiGcJxDlm+LfhfZgi+/D3KW3ivt1b02Qc447uXeaUCvCWCxELjCxcEA6BmgUJMr0E8k85oSP Tj0eT8ZbmqeSaQJc59VsW28wkDVpxzey6qVo8oEFT0B40lVRcaBztXEKW9x8Hy5AK+eTiSnqw LG5n3BhL3ammWUD5uaEITtO/L3K8SpNy9DI3qui3/SMFaP3CQnTUAMGsI/C7cDxC7/Eb+Kxz5 0viM+L9wDUG9BBBZSjva6QSfSQLmeyv0qll0KWegbJe3GhmjonR6RGLnySYTOqRXX7ifI9CzG 1ek5NOnVAI9ojvI9jiLftBxwdOith8jVmZ5t/vJLZuBWigljOVV+OeDEhWSUFF+PUzmKOlPo7 rFzKwgvBUGlQMzwXs0UYSxZAsnrXtjHspelqbJDLk0kt3QlYAWONlmuo3Yw1COfZUtYz0WwTe +JxOj1A4MUAEWMIfMV0rlsQdlbXgJhVamsa/OR5OkI0wyAd6/J/7divxxCvgIUGG/7ZYlIwjx URd9v3mDfSiiIG1QyEWpLXsyrZBwWnNwVhWTqlUV5+prWM2bifDHe661rxp4d3V5NwetodpCn EDPAdcffhl51RecD3erexoof3UcB3NoD+9vZtEYJojxBay+1HhrL5NpuCfAXvFuhg25RC8sNv +IBwpHFunX1KKLtLfMWd0ifiiM4HBljBGMpHXc/h+umgsZAIDW5dlPQ8ENqMzhbPHG/APni2M Pz7jnvWRwTyKafPcxYnB5Qrpm6jzAmYzqZ2GvEQqj0PPyFffhf/KXZysyeWxFpCdwdhAWf+DD IRDWXUngwR+tulZ4mHnK2Z/tuy4KbV/eOqhnf4rm03clanC0lLQIzqnIULrCWv7YehoOP5ENM J+IRtmRwrfU/nfOmw53s5ZaOmQo/fQn9x6er3lZRTXNGD/KLmJZ70YeOI+2gvdFSRPZqyc+aK qMj7KzYA+o0syIvb96AvjHOIokmnaS/rvvxDCP6S322wS3taU2c7rFFUU7I1UNR8wjmMy8tj/ mAOuCAUcqLd1mZz5hM4I3nh8Ylza0r2lR2NSIW+XF0/QkGqgWMXcf4VkYizZUjq90BGbzCKoC jVSvLT0V6ZSQSf9JbWliQpVtqBfeW4Geu599zcUampHPJ67sKrPWzgal/yH0LchHIr8EN4sEQ nFT9I5d1IFf1YcD7jxupmD9TdK8tEP+8fGrS7Zvu1s3pp0NUS5cvHN56542aNBKRgpOtUvPSR HwsDRepGRt68LUG+JRSXywP/GS59WWXafotEykxRq5Ln4qjkUkMuntL0UPXaYfiuYfqEVIElp LA3zZeTo/mMIKIdGYXd+azixBQ4T6Z9x0OHF8zGfDgJJ5DkzxXKIoAf+sIoovoGNJkE7++Kc7 8Khloh3j1z0c2PRkftYARxAyHHb83Ad+a8XQ4nQTbTsfuYpPCYN0TLBMGxG1NMjlZIrqJ7Ta8 RqUTwmtjvsVJFR773Ohle9t8RzvYEHUbca8ucW1jZVQnZWWae0x1HTRbt6W5AUJQTyvg0CVX+ 5BJBYvgKuOCL5/4mOAT2z5lqrTWwafJete9N6GlMXQwbNulDLRrJddBBaAE9GspgGfHeUWi6I B8lI/gBD9tNCdQBkGhR6DVWgvwVtH2uev4V2Rt/eWCMhk0VmNNBOl8lcN49FFCvyXc68dhmEF 3/vp0d7eJzKJ47d4Q2JT4zj84ynvxwf2Ze0FFxSmFgX1HZrnxq6cANvQnAbK/ciyhBunHUMXX c7pV6T2PIsq2iqdh4D3A9GKwZZL3B2G+sZM7Le5fAhcssHsijWuGw1Kh69UBa6gpahTJb8suF W2C+QvcDnZK9kj5hiliiUXDg2q4cuvoR+MRi2B1cjzcplMwILD4y9Y3MlePmVO2XtPqfL1JGT OZczurHfDg+WzWYBk8XL3l2KmuzRsgq7sMQgYpdptdKSdEW/yy9A9gC3fSBv17poAG0xopfYo 6ArfqMzUx0VXuY1XjhjvaM7aysgwCRTGyZgm3OmQ0aKf+Xdx9QzvN2L8y8TiwplkQaNyPpDln ZSG/tMtBzQubZkoOAX3cyxSscpOmvctdeTmEVIBvd0C/ZCY77HiThRnilCCbq2nMhKSWwf00r H/jyweLuLKTBik4UsGsUGWqUU0uKr1iVC//6cfFjEJiowIOiWT0uOjnq5mxcImAfWXexJFrWv SzJaIJE5A1ZSZoF+DLCuCMyYKPsNy4jGFkmu+Um2ZZfrcEz8mkqqMxTAbYxgcOzpSYvaWkyn7 3ytunXYy9LjlSx/NedgjF7s5EIgq2wHQ4DGJiMAqGUSKUoRQR5avYNXu3dbnDjcthCmZKZbFR OWn1o5NeHY2NkzhYeKtxu/k3Y48hnHqmXqdjhqT9SziOqfggfQ7ULQ8aZV5pXaWTo4J6XL9D8 u9pcOFXZhweTs97w1dIbctzoJryBDEXCJNYqq+rRNlmgEtmtMkYO9FEElPytOxN2Twfqwa2wP OGP2BHFi9u4xL3Kjcd8J8hKP3k6SAbKeJh5ldn8aS/7LaC8h5fPur01U5mbgkVc/Z0EDjSS7H 7B1pHzI2tbb5366GgCf99NEvYFMCcsv8Ugdkpa978F0LVYp9zlsIvEue2F3YLXCLh9vMGOhCC 9KH3haTXGm2qGdA3+BoAPSsDm4bcw+rHDvBbBm5CL5iJdGe0jy2r75Gi4KHVW3SnM31ETFXHc 5qtE48fFRUZmP865U1w0Oyhhw7rvePxm8cT0eJJrZ8N9WoihuSp1CqpTpQp8oQihdb0I3j86O 54QJSqGqXmP8n0zNqkpPdwltIQ7XLqEMd3RUDLMr4kq1C/tDNw6xmEvDruMCi+7mh6vz20UVQ h19kKvoHMxGhCoeRV+BbI9+HRSP5XJ0584hKrh34PxM07/CVemjON/wqTBwcpwcJZKqCKfbki n+O5z1cWofqSMh/Z6NwVw/dpyMe+ZLgkBeGHWiHQcNkk+EAQMYnwb6krVYVXNUF3p+COV38eP M1htXKSkWbwnTFZNCvdr6mT1uRJVYWndjigQwkk9ZPPZXA/47GPsSyFoZHQcsPCxFQxlKau26 yPYDr2sRM0p6IrLPBXG7a8B6eT5x2gDohJ70phPbU73W4v8uBwfAtQbECROVrlmv4nHkrAQ7A n+/wex1xxSoNLPegC6S/qmab6GixG3vtBDfDt2+2tt6x6IFiJ4OATvkExzm5JW3vNOE9D3UdY /Dsxm9iJ76nK/yhLIYgT5vDhY6AK/DMFBNgVEQH7vClIm1vDjpJ6pt5lYvV8HfEmRH7zdQzSO 9hVu3URaF6NM951djOCTgV0ftG04wVSKbgBZdZvESUEBtWvZ4H8SwBEOV/v1JbdTdGMmG240E c1M3JI+3nyszgev0P+a5qXDgOBqWYXNial0LUwsZcWv17XgQJQn/BI= Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 >=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; 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 =2D-=20 GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B