Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8BC866335BB for ; Mon, 24 May 2010 14:11:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 47440-05 for ; Mon, 24 May 2010 17:11:25 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sargon.lncsa.com (mail.lncsa.com [213.215.28.11]) by mail.postgresql.org (Postfix) with ESMTP id 2660B63249C for ; Mon, 24 May 2010 14:11:25 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by sargon.lncsa.com (Postfix) with ESMTP id BCBFB3025425 for ; Mon, 24 May 2010 19:11:23 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at lncsa.com Received: from sargon.lncsa.com ([127.0.0.1]) by localhost (sargon.lncsa.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Ts0Vm1PNk4ME for ; Mon, 24 May 2010 19:11:23 +0200 (CEST) Received: from zenon.apartia.fr (zenon.apartia.fr [10.0.3.1]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "zenon.apartia.fr", Issuer "ca.apartia.fr" (verified OK)) by sargon.lncsa.com (Postfix) with ESMTP id 9F8513025419 for ; Mon, 24 May 2010 19:11:23 +0200 (CEST) Received: from delos.apartia.fr (delos.apartia.fr [10.0.3.107]) by zenon.apartia.fr (Postfix) with ESMTP id 45E2F5B93F8E6 for ; Mon, 24 May 2010 19:11:23 +0200 (CEST) Received: by delos.apartia.fr (Postfix, from userid 1000) id C6CC020283886; Mon, 24 May 2010 19:11:22 +0200 (CEST) Date: Mon, 24 May 2010 19:11:22 +0200 From: Louis-David Mitterrand To: pgsql-sql@postgresql.org Subject: Re: safely exchanging primary keys? Message-ID: <20100524171122.GA20077@apartia.fr> Mail-Followup-To: pgsql-sql@postgresql.org References: <20100524085101.GA21249@apartia.fr> <20100524170030.GB19481@apartia.fr> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20100524170030.GB19481@apartia.fr> User-Agent: Mutt/1.5.20 (2009-06-14) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201005/109 X-Sequence-Number: 34157 On Mon, May 24, 2010 at 07:00:30PM +0200, Louis-David Mitterrand wrote: > On Mon, May 24, 2010 at 02:38:39PM +0000, Tim Landscheidt wrote: > > you use something simple like (untested): > > > > | UPDATE cabin_type > > | SET id_cabin_type = > > | CASE > > | WHEN id_cabin_type = id1 THEN > > | id2 > > | ELSE > > | id1 > > | END > > | WHERE id_cabin_type IN (id1, id2); > > Nice, thanks. Ah, but this won't work as the UNIQUE PK constraint is in force.