重複したレコードを１件だけ残して削除したい

Question

postgreSQL(ver9.3.5)のデータベースを、phpPgAdmin(ver5.1)で操作しています。

phpPgAdmin でテーブルを表示させた時、
操作欄（編集ボタン／削除ボタン）が付くテーブルと付かないテーブルとがあり、
原因を調べてみた所、操作欄が無いテーブルには「プライマリキー」が
設定されていない事が原因と分かりました。

そこで早速、プライマリーキーを追加してみたところ、
レコードが重複しているというエラーが出て、
プライマリーキーを追加することが出来ませんでした。

SELECT文で確認してみると、
確かにレコードが重複していて、
しかも全フィールド内容が一致してしまっています。
あちゃー。

テーブル名 order_progress_table
--------------------------------------------------------
id 	order_num 	progress_info 	processing_date
--------------------------------------------------------
19314	08071401	4	2008-07-14 09:25:15.423073
19314	08071401	4	2008-07-14 09:25:15.423073
19314	08071401	4	2008-07-14 09:25:15.423073
--------------------------------------------------------

１件だけレコードを残して削除したいのですが、
以下のSQL文だと、全て消えてしまいそうです。

DELETE FROM order_progress_table WHERE ID = 19314;

どのようにすれば、１件だけ残して削除することが出来ますでしょうか？

Answer 1

既に解決済みのようですが、中間テーブルを用いない方法として以下のようなSQLをご紹介します。
⇒参考
IDをプライマリキーにしたい場合（IDが重複するデータを１つのみ残して削除する）

DELETE FROM order_progress_table  
 USING
   ( SELECT id, max(ctid) AS ctid FROM test GROUP BY id ) AS X
    WHERE X.id = order_progress_table.id AND X.ctid <> order_progress_table.ctid ;

Answer 2

PostgreSQL9.1の新機能の「WITH句とINSERT,UPDATE,DELETEを同時に」
http://lets.postgresql.jp/documents/technical/9.1/1#writable-cte
を見ながらPostgreSQL9.5で作ってみました :-)

with tmp as(delete from order_progress_table Returning *)
Insert Into order_progress_table select distinct * from tmp;

Answer 3

PostgreSQLにはWITH句で、削除しながらを削除データを別テーブルに書き出せる例が掲載されていました。
削除後に、別テーブルのデータを一意化して 元のテーブル戻す(INSERT)すれば解決ですね。
よくかんがえたら、別にWITH句使わなくてもできますね。
削除予定分を別テーブルに取り出してから、削除すれば同じことですから、、、

Answer 4

良いコマンドが思いつかないのでとりあえずのSQL

create temp table tmp_order_progress_table as select distinct * from order_progress_table;
truncate table order_progress_table;
insert into order_progress_table select * from tmp_order_progress_table;

３ステップも必要です。

念の為に実行前に

pg_dump -U user -t order_progress_table db > order_progress_table.dmp

を行ってからです。

Comments

[chibi_kuma]

A.Ichi さん、ありがとうございます！！
すごいです！
一回でやろうとすると難しいけれど、段階を踏めばシンプルに出来るんですね。
なにやら小難しいことばかり考えていておりました。
自分の知らないコマンドもあり、勉強になりました。

ＤＢにコマンドラインではアクセスが出来なかったので、pg_dumpはできませんでした。
phpPgAdminでテーブルをエクスポーtトしてからやりました。
そこまでケアしていただき、感謝です。

ありがとうございました。またよろしくお願い致します。

Answer 5

MySQLなら LIMIT 区で１個ずつできます。
１個ずつなので個数が多い場合は、他の方法。
CREATE TABLE temp.chk (f1 VARCHAR(8), f2 VARCHAR(8));
INSERT INTO temp.chk values('0001','abc'),('0001','def');
DELETE FROM temp.chk WHERE f1='0001' LIMIT 1;
SELECT * FROM temp.chk;
件数が多い場合は、一旦外部ファイルに書き出す
SELECT DISTINCT * FROM temp.chk INTO OUTFILE '/temp/chk.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
TRUNCATE TABLE temp.chk;
LOAD DATA LOCAL INFILE '/temp/chk.txt'
INTO TABLE temp.chk
CHARACTER SET utf8mb4 FIELDS ESCAPED BY '' TERMINATED BY ',' ENCLOSED BY '"' ;
PG_SQLにも対応する機能があっても良さそうですね。
別テーブルに書き出す。
CREATE TABLE temp.dmp SELECT DISTINCT * FROM temp.chk;
DROP TABLE temp.chk;
ALTER TABLE temp.dmp RENAME chk;