This article is half-done without your Comment! *** Please share your thoughts via Comment ***
I have already written a similar article to delete duplicate records in SQL Server and MySQL.
Here, You can also access that articles.
Recently, I got one request for one script to delete duplicate records in PostgreSQL.
Most of the Database Developers have such a requirement to delete duplicate records from the Database.
Like SQL Server, ROW_NUMBER() PARTITION BY is also available in PostgreSQL.
I have prepared this script, using simple inner query with the use of ROW_NUMBER() PARTITION BY clause.
Create a sample table:
|
1 2 3 4 5 |
CREATE TABLE tbl_RemoveDuplicate ( ID INTEGER PRIMARY KEY ,Name VARCHAR(150) ); |
Insert few duplicate records:
|
1 2 3 4 5 |
INSERT INTO tbl_RemoveDuplicate VALUES (1,'ABC'),(2,'XYZ') ,(3,'XYZ'),(4,'RFQ') ,(5,'PQR'),(6,'EFG') ,(7,'EFG'),(8,'ABC'); |
Except one, Delete all duplicate records:
|
1 2 3 4 5 6 7 |
DELETE FROM tbl_RemoveDuplicate WHERE ID IN (SELECT ID FROM (SELECT id, ROW_NUMBER() OVER (partition BY Name ORDER BY ID) AS RowNumber FROM tbl_RemoveDuplicate) AS T WHERE T.RowNumber > 1); |
Check the result:
|
1 2 3 4 5 6 7 8 9 |
SELECT *FROM tbl_RemoveDuplicate; id | name ----+------ 1 | ABC 2 | XYZ 4 | RFQ 5 | PQR 6 | EFG (5 rows) |
