PostgreSQL のデータベースで重複レコードを見つけて削除する

1. PostgreSQL データベース スキーマ
2. PostgreSQL データベースの状態
3. PostgreSQL で重複レコードを見つける
4. PostgreSQL の重複レコードを削除する
5. まとめ

この記事では、さまざまなコマンドを使用して PostgreSQL で重複するレコードを見つけて削除する方法について説明します。

データベース内の重複レコードを検索するために、多数のクエリを記述して使用できます。 その後、同じクエリを微調整して、データベース内の重複レコードを削除できます。

重複レコードを見つけるのに役立つクエリの一部を以下に示します。 同じ目的のためにさまざまな代替クエリが存在するため、実験を続けて新しい解決策を見つけることができます。

PostgreSQL データベース スキーマ

以下に設計されたスキーマは、クエリのみに集中できるように記事全体で使用されます。 すべてのクエリは、クエリの動作をよりよく理解するためにスキーマに従って設計されています。

CREATE TABLE Student (
    sid INT PRIMARY KEY,
    sname VARCHAR (50),
    sage INT,
    semail VARCHAR(80)
);

PostgreSQL データベースの状態

次のレコードが Student テーブルに追加され、レコードが入力されます。

INSERT INTO Student VALUES (1, 'A', 15, 'a@gmail.com');
INSERT INTO Student VALUES (2, 'A', 17, 'ab@gmail.com');
INSERT INTO Student VALUES (3, 'B', 17, 'b@gmail.com');
INSERT INTO Student VALUES (4, 'AB', 21, 'ab@gmail.com');

データベースのスキーマと状態は、テーブル内の sid 列のみが一意であることを明確に示しています。 そうしないと、すべての列が重複レコードを保持できます。

すべてのレコードが正常に挿入された後の Student テーブルの状態を以下に示します。

sname、sage、および semail に重複した値が見られます。

PostgreSQL で重複レコードを見つける

すでに重複レコードを含む列に一意制約を適用すると、エラーが生成されます。 後でその属性に一意の制約を適用するために、異なるレコードを持つことが不可欠です。

重複レコードが見つかる理由は他にもいくつかあります。 PostgreSQL でクエリを記述して、重複レコードを除外できます。

以下にクエリを示します。

クエリ 1

重複レコードを見つけるのに役立つ非常に基本的なクエリは次のとおりです。

SELECT sname, count(*)
FROM Student
GROUP BY sname
HAVING count(*) >1

上記のクエリは非常に基本的なものです。 sname 列に従ってデータをグループ化し、同じ名前の学生が見つかった回数を表示します。

クエリ結果は次のとおりです。

注: sage 列の重複をチェックしたい場合は、クエリの sname 列に置き換えます。 さらに、名前と年齢が同じ重複レコードを検索する場合は、両方の列をクエリに追加します。

クエリ 2

ネストされたクエリに精通している場合、これは重複する値をチェックするための簡単なソリューションになる可能性があります。 これは、上記のクエリと同様のクエリです。 ただし、ネストされたクエリの概念が含まれています。

SELECT *
FROM Student ou
WHERE (
    SELECT count(*)
    FROM Student inr
    WHERE inr.sname = ou.sname
) > 1

クエリは、期待どおりに同じ結果を提供します。 重複レコードを持つ sname のレコード全体を表示します。

他の属性で重複するレコードを見つけたい場合は、上記のクエリの sname を選択した属性に置き換えることができます。

クエリから生成された結果は次のとおりです。

クエリ 3

すべての属性で同じ値を持つ重複レコード全体を見つけるには、次の単純なクエリを使用します。

SELECT (Student.*)::text, count(*)
FROM Student
GROUP BY Student.*
HAVING count(*) > 1

上記のクエリは、すべての属性を取得します。 この場合、sid、sname、sage、および semail を取り、すべての属性で正確な値を使用して同じレコードが繰り返される回数をカウントします。

このスキーマでは、sid 属性が主キーです。 したがって、完全なレコードの複製は許可されません。 したがって、クエリは結果を生成しません。

クエリ 4

特定のテーブルで重複するレコードを見つけるために使用できる別のクエリを以下に示します。

SELECT *
FROM (
    SELECT sname, sage, semail, ROW_NUMBER() OVER
        (
        PARTITION BY sname
        ORDER BY sname, sage, semail ASC
        ) AS Row FROM Student
    ) dups
WHERE dups.Row >1

これは、重複行を検索し、重複レコードの sname、sage、および semail を表示するネストされたクエリです。 クエリ結果は次のとおりです。

PostgreSQL の重複レコードを削除する

重複するレコードを見つけたら、それらをテーブルから削除することをお勧めします。 重複レコードを削除するには、いくつかのクエリを使用できます。 ただし、この記事にはそのようなクエリが 1つだけ含まれています。

クエリ 1

上記の 4つのクエリは、微調整して重複レコードを削除することで使用できます。 演習として、これらのクエリを自分で試してみることができます。

この記事では、テーブルから重複レコードを削除する別のクエリを含めました。

DELETE
FROM Student s1
USING Student s2
WHERE s1.sname = s2.sname AND s1.sid < s2.sid

上記のクエリは、A という名前の Student テーブルから 1つのレコードを削除します。 クエリは、最新のレコードを除くすべてのレコードを削除し、成功のメッセージを生成します。

削除後のテーブルの状態を以下に示します。

まとめ

データベース内の重複レコードを見つけるために、多数のクエリを使用できます。 これらのクエリを微調整すると、それらのレコードも削除できます。

さまざまなクエリを試して、要件に合った最適なクエリを見つけてください。