PostgreSQLで重複するデータの内の1件だけを削除する(システム列のctidを使った削除)

基本こんな状態になることはないはずなので、こんな対応する必要がある事自体が異常ではありますが、どうすれば良いのか調べて対処したので記録。

状況

• とあるテーブルでid列がプライマリキーに設定されていて、重複は許可されていない状況
• id列以外にname列もあり
• 通常であればid列が重複することなく管理されており、削除する場合はDELETEでid指定で消せば問題ない
• 例えば以下のような感じでデータが登録されている

id	name
no-111	test data 1
no-222	test data 2

• このような状況で、PostgreSQLのWALデータが破損したことをきっかけに以下のように何故か重複されたデータが入った状態になってしまった

id	name
no-111	test data 1
no-222	test data 2
no-111	test data 1

重複したデータを削除するには

• no-111のデータが2つはいってしまったので削除する

• そもそもデータおかしいので本来はバックアップデータとかからの復旧が望ましいですが、ここではその対応はせずにクエリで復旧を試みる

• MySQLとかだと、DELETE文にlimitを指定できるようなので、以下のように2個あるno-111のデータの内1件だけ削除っていうのも可能。

DELETE FROM testtable WHERE id = 'no-111' LIMIT 1;

• PostgreSQLだと、DELETE文でLIMIT句使えない。かつ上記のように全カラム同じ値になっているのでWHERE句でフィルタができないので、そのままでは対応が難しい。
• PostgreSQLにはシステム列を持っているのでこれを活用する。
  • ctidという列を持っている
  • これは、行バージョンの物理的な位置を示す列情報
• 例えば上記のtesttableの例だと以下のようなイメージ
• SELECT ctid,id,name FROM testtable;を実行すると以下のような感じで出力される

ctid	id	name
(0,1)	no-111	test data 1
(0,3)	no-222	test data 2
(41,3)	no-111	test data 1

• ctidを指定して削除ができるので以下のように実行すればデータ削除可能

DELETE FROM testtable WHERE ctid = '(41,3)';

補足

• そもそもこのような主キーが重複するデータが入るのがおかしいので状況を見てみたところ、idをWHERE句で指定すると重複が発生する前の元のデータのみが取れる状態になっている

SELECT * FROM testtable WHERE id = 'no-111';

id	name
no-111	test data 1

• LIKE句で指定すると2件取れてくる

SELECT * FROM testtable WHERE id LIKE 'no-111%';

id	name
no-111	test data 1
no-111	test data 1

• idの末尾にビューでは見えない文字が含まれているのか？と思い絞り込んでみても特に入っているわけではなさそう(末尾をLIKEで任意の一文字指定してもひっかかるので)

SELECT * FROM testtable WHERE id LIKE 'no-11_';

id	name
no-111	test data 1
no-111	test data 1

• かつid列の長さを出しても全く同じ。

SELECT length(id),id,name FROM testtable WHERE id LIKE 'no-111%';

length(id)	id	name
6	no-111	test data 1
6	no-111	test data 1

以下のページのやり取りでもあるように、おそらく主キーの情報が破損してしまっている可能性が高いので、こういったケースはバックアップデータから復旧するなど、ちゃんときれいに作り直した方がいいですね。