PostgreSQLで重複行を1件だけ削除する方法（ctid活用） - 株式会社テイク

はじめに

株式会社テイク－ワンのM・Kです。
今回はPostgreSQLでのミスについて書いていこうと思います。PostgreSQL特有の仕様にハマって削除に苦労したので、備忘録としてまとめます。

やらかし：INSERT文を2回叩いてしまった

テスト用に次のようなテーブルを作っていました。


INSERT INTO test (id, name, kanji)
VALUES  (1, 'taro', '太郎'), (2, 'hanako', '花子'), (3, 'jiro', '次郎');

これを誤って2回実行してしまい、テーブルには同じデータが2セット入ってしまいました。

 id |  name  | kanji
----+--------+--------
  1 | taro   | 太郎
  2 | hanako | 花子
  3 | jiro   | 次郎
  1 | taro   | 太郎
  2 | hanako | 花子
  3 | jiro   | 次郎

本来idはユニークにすべきですが、テスト用なので制約を付けていなかったので、重複レコードができてしまいました。

つまずきポイント：DELETE + LIMIT を使おうとしてエラーに

重複したレコードの片方だけを消したくて、次のようにSQLを書きました。

DELETE FROM test WHERE id = 1 LIMIT 1;

すると PostgreSQL ではエラー。

DELETE FROM test WHERE id = 1 LIMIT 1;
ERROR:  "limit"またはその近辺で構文エラー

何度やってもうまくいきませんでした。

なぜか？

PostgreSQL の DELETE 文は LIMIT をサポートしていないため。
MySQLでは使えるが、PostgreSQLでは構文エラーになる。

解決：PostgreSQLで「1件だけDELETE」する方法を見つける。

1.ctidを使って、特定の行だけを削除する。
PostgreSQLでは、ctid を使うことで物理的に特定の行だけをピンポイントで削除できます。
ctidはPostgreSQL が内部的に持っている「ページ番号＋タプル番号」を表す隠し列です。
コマンド実行例

SELECT ctid,* FROM test

  ctid  | id |  name  | kanji
--------+----+--------+-------
 (0,37) |  1 | taro   | 太郎
 (0,38) |  2 | hanako | 花子
 (0,39) |  3 | jiro   | 次郎
 (0,40) |  1 | taro   | 太郎
 (0,41) |  2 | hanako | 花子
 (0,42) |  3 | jiro   | 次郎

下の 3 行（(0,40)〜(0,42)）が重複しているレコードです。

2.重複しているレコードを削除する。

DELETE FROM test WHERE ctid IN ('(0,40)', '(0,41)', '(0,42)');
DELETE 3

SELECT * FROM test;

 id |  name  | kanji
----+--------+-------
  1 | taro   | 太郎
  2 | hanako | 花子
  3 | jiro   | 次郎

重複が消えて、元の3行だけが残すことができました!

4. 今回の学び

PostgreSQLのDELETEではLIMITが使えない
テスト用でもPRIMARY KEY制約は付けたほうが安全（今回のような事故を防げる）
重複データを扱うときはctidが便利（PostgreSQL特有の機能）

おわりに

今回のような「ちょっとしたミス」でも、データベースの仕様を理解していなかったので、苦労しました。
同じように PostgreSQLでつまずいた方の助けになれば幸いです。