PostgreSQL 8.3.8 データベースに、キーや制約がなく、まったく同じ値を持つ複数の行があるテーブルがあります。
重複をすべて削除し、各行のコピーを 1 つだけ保持したいと思います。
特に、重複を識別するために使用できる列が 1 つあります (「キー」という名前)。つまり、異なる「キー」ごとにエントリが 1 つだけ存在することになります。
どうすればこれを実行できますか? (理想的には、単一の SQL コマンドを使用します。)
この場合、速度は問題ではありません (行が数行しかないため)。
ベストアンサー1
より速い解決策は次のとおりです。
- 重複の最初の出現箇所を見つける。
- 次に、最初の重複発生以外のすべての行を削除します。
次のようになります。
DELETE FROM dups a USING (
SELECT MIN(ctid) as ctid, key
FROM dups
GROUP BY key HAVING COUNT(*) > 1
) b
WHERE a.key = b.key
AND a.ctid <> b.ctid
このソリューションでは、どの行を保持するかを制御できないことに注意してください。
おもちゃの例
CREATE TABLE people (
name varchar(50) NOT NULL,
surname varchar(50) NOT NULL,
age integer NOT NULL
);
INSERT INTO people (name, surname, age) VALUES
('A.', 'Tom', 30),
('A.', 'Tom', 10),
('B.', 'Tom', 20),
('B', 'Chris', 20);
-- The inner command to find duplicates first occurences:
SELECT MIN(ctid) as ctid, name, surname
FROM people
GROUP BY (name, surname) HAVING COUNT(*) > 1;
DELETE FROM people a USING (
SELECT MIN(ctid) as ctid, name, surname
FROM people
GROUP BY (name, surname) HAVING COUNT(*) > 1
) b
WHERE a.name = b.name
AND a.surname = b.surname
AND a.ctid <> b.ctid;
SELECT * FROM people;
内部リクエストの出力は次のようになります。
| ctid | 名前 | 姓 |
|---|---|---|
| （０，１） | A. | トム |
そして、最終的なリクエスト（削除後）の出力は次のようになります。
| 名前 | 姓 | 年 |
|---|---|---|
| A. | トム | 30 |
| B. | トム | 20 |
| B | クリス | 20 |