読者です 読者をやめる 読者になる 読者になる

UNIQUE 制約が付与された値の入れ替え方法

PostgreSQL

お題はタイトルの通り、UNIQUE 制約がついた行の値を 1 トランザクションで入れ替えたい、と。PostgreSQL のバージョンは 8.4 。例えばこんなテーブルを作り

CREATE TABLE sample (id PRIMARY KEY, value INT UNIQUE);

以下のような状態で、10 と 20 の値を入れ替えたい。

id value
1 10
2 20

結論からいうと、あまりスマートな方法が思いつかず、一旦値を制約に違反しない値 (max(value) + 1 とか、勿論 LOCK かけてから) に事前更新してから、入れ替えるといった泥臭い方法をとらざるを得ない模様。(上手くいかなかった) 対応方法ですが、調べたのは二点。

CASE 文使う方法

ここにある CASE 文を使う方法は、少なくとも PostgreSQL 8.4 環境では以下の通り怒られ。

=> update sample set value = (case id when 1 then 20 when 2 then 10 else value end) where id in (1,2);
ERROR:  duplicate key value violates unique constraint "sample_value_key"

SET CONSTRAINTS DEFFERED

SET CONSTRAINTS 文で制約チェックを遅延させる方法。ただ、これ PostgreSQL 8.4 では FK 制約にしか使えないようで。ここ にある通り、PostgreSQL 9.0 から UNIQUE 制約でも DEFFERED 出来るようなのですが。。。

他に良いアイディアないかな。。。