Delete Duplicates

Create Table Of Elves

-- Create table called elves
CREATE TABLE elves (
    -- string variable
    name varchar(255),
    -- integer variable
    age int,
    -- string variable
    race varchar(255),
    -- string variable
    alive varchar(255)
)

Insert Rows Into Elf Table

INSERT INTO elves (name, age, race, alive)
VALUES ('Dallar Woodfoot', 25, 'Elf', 'Yes'),
       ('Cordin Garner', 29, 'Elf', 'Yes'),
       ('Keat Knigh', 24, 'Elf', 'Yes'),
       ('Keat Knigh', 24, 'Elf', 'Yes'),
       ('Keat Knigh', 24, 'Elf', 'Yes'),
       ('Keat Knigh', 24, 'Elf', 'Yes'),
       ('Colbat Nalor', 124, 'Elf', 'Yes')

View Elves Table

-- Retrieve all rows from the view Elf
SELECT * FROM elves
nameageracealive
Dallar Woodfoot25ElfYes
Cordin Garner29ElfYes
Keat Knigh24ElfYes
Keat Knigh24ElfYes
Keat Knigh24ElfYes
Keat Knigh24ElfYes
Colbat Nalor124ElfYes

Drop Duplicates

Note: Normally we would use a unique identify column (e.g. person ID, product ID, etc.). However, since we don’t have a unique ID column we can use PostgreSQL’s internal system column, ctid. Full documentation on ctid and other system columns in avaliable here.

-- Delete from the elves, calling it copy1
DELETE FROM elves copy1
-- Using a second copy of elves, called copy2
USING elves copy2
-- Where the internal PostgreSQL system column, ctid is smaller
WHERE copy1.ctid < copy2.ctid
  -- And all other columns are the same
  AND copy1.name = copy2.name
  AND copy1.age = copy2.age
  AND copy1.race = copy2.race
  AND copy1.alive = copy2.alive
nameageracealive
Dallar Woodfoot25ElfYes
Cordin Garner29ElfYes
Keat Knigh24ElfYes
Colbat Nalor124ElfYes