Question 89529 in PostgreSQL Question posted in
The official documentation can be found here.

Remove duplicate records with same ID – Postgresql

Wondering if it is possible to keep just one record from duplicates. In this scenario, the ID is also duplicate. I know the best way is that ID is unique, but in this scenario is not. Then, I have three records like these ones:

id name point closest_community_id
1 Secondary School POINT (-121.94291423 49.17457331) 126
1 Secondary School POINT (-121.94291423 49.17457331) 126
1 Secondary School POINT (-121.94291423 49.17457331) 126

I can find the duplicates with this query:

    select id, name, point, closest_community_id  
    from primary_location pl 
    group by pl.id, name, point, closest_community_id
    having count(pl.id) > 1

But how can I keep just one record from the duplicates when they have the same ID?

Tags:

2

Answers


  1. 0

    Use the ctid:

    delete from primary_location pl
where exists
(
  select null
  from primary_location pl2
  where pl2.id = pl.id
  and pl2.name = pl.name
  and pl2.point = pl.point
  and pl2.closest_community_id = pl.closest_community_id
  and pl2.ctid > pl.ctid
);
    Login or Signup to reply.
  2. 0

    Would you like to try

    select distinct id, name, point, closest_community_id  
from primary_location pl
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search