CODEWITHSUNDEEP
postgresql
Mattudi
Mattudi

Reputation: 21

Delete Duplicate from table in postgresql

-- Create the table CREATE TABLE colour ( col1 VARCHAR, col2 VARCHAR, col3 VARCHAR );

-- Insert sample data INSERT INTO colour (col1, col2, col3) VALUES ('red', 'blue', 'black'), ('grey', 'red', 'white'), ('pink', NULL, 'blue'), ('red', 'blue', 'black'), ('grey', 'red', 'white'), ('pink', NULL, 'blue');

In PostgreSQL, I want to delete exact duplicate rows from the table and only want to keep one of them. What would you suggest to me?

I tried using CTE but not get the required result.

Upvotes: 1

Views: 30

Answers (1)

MJKing
MJKing

Reputation: 88

You can try something like this:

WITH duplicates AS (
SELECT
    ctid,
    ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY ctid) AS rn
FROM
    colour
 )
DELETE FROM colour
WHERE ctid IN (
SELECT ctid
FROM duplicates
WHERE rn > 1
);

First you have to find the duplicated rows, and use the internal field ctid in the CTE to identify the rows you want to delete

Upvotes: 0

Related Questions