Delete Duplicate from table in postgresql

Question

-- 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.

MJKing · Accepted Answer · 2024-07-30 10:12:37Z

0

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

answered Jul 30, 2024 at 10:12

MJKing

885 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Belayer Over a year ago

If the above answer resolved your issue, or lead you to the solution, then kindly accept it. This aids future users having the same or similar issue and removes it from the unanswered queue. Please do not leave a successfully answered question as unanswered.

Collectives™ on Stack Overflow

Delete Duplicate from table in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related