Removing Duplicate Rows in PostgreSQL with multiple columns

Question

I have a table "votes" with the following columns: voter, election_year, election_type, party I need to remove all duplicate rows of the combination of voter and election_year, and I'm having trouble figuring out how to do this.

I ran the following:

WITH CTE AS(
SELECT voter, 
       election_year,
       ROW_NUMBER()OVER(PARTITION BY voter, election_year ORDER BY voter) as RN

FROM votes
)
DELETE
FROM CTE where RN>1

based on another StackOverflow answer, but it seems this is specific to SQL Server. I've seen ways to do this using unique ID's, but this particular table doesn't have that luxury. How can I adopt the above script to remove the duplicates I need? Thanks!

EDIT: Per request, creation of the table with some example data:

CREATE TABLE public.votes
(
    voter varchar(10),
    election_year smallint,
    election_type varchar(2),
    party varchar(3)
);

INSERT INTO votes
    (voter, election_year, election_type, party)
VALUES
    ('2435871347', 2018, 'PO', 'EV'),
    ('2435871347', 2018, 'RU', 'EV'),
    ('2435871347', 2018, 'GE', 'EV'),
    ('2435871347', 2016, 'PO', 'EV'),
    ('2435871347', 2016, 'GE', 'EV'),
    ('10215121/8', 2016, 'GE', 'ED')
;

"based on another StackOverflow answer, but it seems this is specific to SQL Server." This query looks perfect PostgreSQL syntax to me. PostgreSQL also support WITH .. AS (common table expressions) and ROW_NUMBER() OVER (....) just fine.. "How can I adopt the above script to remove the duplicates I need? Thanks!" Very hard to say without table structure and example data. Check stackoverflow.com/help/how-to-ask section "Help others reproduce the problem" — Raymond Nijland
– Raymond Nijland, Commented Aug 19, 2018 at 1:42
I apologize, the error given is "[42P01] ERROR: relation "cte" does not exist Position: 157" — JGrindal
– JGrindal, Commented Aug 19, 2018 at 1:52

mankowitz · Accepted Answer · 2018-08-19 02:35:02Z

26

Here's an option

DELETE FROM votes T1
    USING   votes T2
WHERE   T1.ctid < T2.ctid 
    AND T1.voter = T2.voter 
    AND T1.election_year  = T2.election_year;

See http://sqlfiddle.com/#!15/4d45d/5

answered Aug 19, 2018 at 2:35

mankowitz

2,1151 gold badge19 silver badges45 bronze badges

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

Comments

sticky bit · Accepted Answer · 2018-08-19 02:34:09Z

19

Delete from or updating CTEs doesn't work in Postgres, see the accepted answer of "PostgreSQL with-delete “relation does not exists”".

Since you have no primary key you may (ab)use the ctid pseudo column to identify the rows to delete.

WITH
cte
AS
(
SELECT ctid,
       row_number() OVER (PARTITION BY voter,
                                       election_year
                          ORDER BY voter) rn
       FROM votes
)
DELETE FROM votes
       USING cte
       WHERE cte.rn > 1
             AND cte.ctid = votes.ctid;

db<>fiddle

And probably think about introducing a primary key.

answered Aug 19, 2018 at 2:34

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

4 Comments

JGrindal Over a year ago

I tried this, but I'm getting [42703] ERROR: column cte.ctid does not exist

sticky bit Over a year ago

@JGrindal: Sure you copied the statement completely? Or have you just edited yours? If, have you also added the ctid to the SELECT in the CTE?

JGrindal Over a year ago

Yup, forgot ctid in my CTE. Thanks!

Chalist Over a year ago

plus for dbfiddle :D

Zaynul Abadin Tuhin · Accepted Answer · 2018-08-19 02:58:11Z

The ctid field is a field that exists in every PostgreSQL table and is unique for each record in a table and denotes the location of the tuple. You did almost right just need ctid as you have no unique id for each row

;WITH CTE AS(
SELECT ctid,voter, 
       election_year,
       ROW_NUMBER()OVER(PARTITION BY voter, election_year ORDER BY voter) as RN

FROM votes
)
delete  FROM votes v where v.ctid in (select CTE.ctid from  CTE where CTE.RN>1)

http://sqlfiddle.com/#!17/4d45d/14

Collectives™ on Stack Overflow

Removing Duplicate Rows in PostgreSQL with multiple columns

3 Answers 3

Comments

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Linked

Related