Postgresql: Creating unique values from a non-unique field

Suppose you have a database table with a field that currently contains non-unique values. Now suppose you want to update the aforementioned field to contain only unique values, but you don’t want to update any values that were already unique.

To do this you can use the following SQL query:

UPDATE sometable

SET somefield=somefield||uniquekeyfield

WHERE uniquekeyfield NOT IN
( SELECT max(dup.uniquekeyfield)

FROM sometable as dup GROUP BY dup.somefield );

This query will update each non-unique value in somefield to have the matching value from uniquefield concatenated to it. All values that were already unique will stay untouched.

For example, if you had the following table:

 id  | username
---------------
 7   | mike
 8   | dave
 9   | mike
 10  | mike
 11  | john
 12  | jeff

And you decided that you want to change the username field to be unique, you could run the query and your table would become:

 id  | username
---------------
 7   | mike7
 8   | dave
 9   | mike9
 10  | mike
 11  | john
 12  | jeff

All usernames are now unique, and only minimal changes have been made.