以下のようなテーブルがあります。

articlesテーブル

create table articles (
	article_id serial,
	sort_order integer not null,
	content text,
	PRIMARY KEY(article_id),
	UNIQUE(sort_order)
)

article_idが主キー。sort_orderにはユニーク制約が付いています。

そして、このテーブルには以下のようなレコードがあります。

articlesテーブルのレコード

insert into articles (sort_order, content) values (1, 'Aさんの記事です');
insert into articles (sort_order, content) values (2, 'Bさんの記事です');

UI上では、sort_orderの順に記事が並ぶものとします。

本題

ここで、Aさんの記事とBさんの記事の並び順を入れ替えたい場合、どのようなSQLを発行すればよいか？

一旦以下で書きました。

sort_orderの入れ替え(動作しない)

update articles
set sort_order = 
  case sort_order 
	when 1 then 2
	when 2 then 1
	else sort_order
end

sort_orderが1のものを2にし、同時に2のものを1にしたいわけです。

ところが、以下のエラーが表示されます

ERROR:  重複したキー値は一意性制約"articles_sort_order_key"違反となります
DETAIL:  キー (sort_order)=(2) はすでに存在します。
SQL 状態： 23505

SQLServerではできたのに！

PostgreSQLでは、内部的に二度にUPDATEが分かれているのでしょうか？

対応

ネットを検索した限りでは、1トランザクション内でsort_orderを一旦別の値にupdateして戻すやり方が多いようでした。以下のような形。

このように1件ずつ更新していく方法です。
これだと確かに、一意誓約違反にならずにsort_orderの入れ替えができます。

しかし、SQLを3回実行している...
が、1回でできる方法は無さそうだ...

ということで、SQLの発行2回で入れ替えられる以下の方法をとることにしました。

具体的には以下のSQL

SQL1回目

update articles
set sort_order = 
  case sort_order 
	when 1 then 2*(-1)   -- 更新したい値*(-1)
	when 2 then 1*(-1)   -- 更新したい値*(-1)
	else sort_order
end

SQL2回目

update articles
set sort_order = sort_order * (-1)
where sort_order < 0   -- 負の数のものを正の数にする

これでSQL2回でできました

場合によりけりですが、参考になれば幸いです。
ありがとうございました