CODEWITHSUNDEEP
postgresqlduplicatesrename
Til_Infinity95
Til_Infinity95

Reputation: 11

How to rename duplicates in PostgreSQL

I have a PostgreSQL database that has multiple duplicate names in one column.

For example, in column "itemname", there are items such as:

sandwich
ginger brew
cream cheese
cream cheese
beer
gum
gum
beer
cream cheese
sandwich
nutella
beer
...

as you can see, there are duplicate itemnames and I'm looking for a way to change all duplicate names by adding number at the end of the item like following:

sandwich
ginger brew
cream cheese
cream cheese_2
beer
gum
gum_2
beer_2
cream cheese_3
sandwich_2
nutella
beer_3
...

Is there a way to rename multiple duplicate item names?

Thank you

Upvotes: 1

Views: 778

Answers (1)

eshirvana
eshirvana

Reputation: 24593

first you can find the dups:

select *, row_number() over (partition by itemname order by itemname) rn
from yourtable

and you can update them like this:

update yourtable t
set itemname = itemname || '_' || rn
from (
       select *, row_number() over (partition by itemname order by itemname) rn
       from yourtable
) w
where w.rn > 1
and w.Id = t.Id

Upvotes: 5

Related Questions