Renumber duplicates to make them unique

Question

       Table "public.t"
 Column |  Type   | Modifiers
--------+---------+-----------
 code   | text    |
 grid   | integer |

The codigo column, although of type text, has a numeric sequence which has duplicates. The grid column is a unique sequence.

select * from t order by grid;
 code | grid
------+------
 1    |    1
 1    |    2
 1    |    3
 2    |    4
 2    |    5
 2    |    6
 3    |    7

The goal is to eliminate the duplicates in the code column to make it unique. The result should be similar to:

 code | grid
------+------
 1    |    1
 6    |    2
 4    |    3
 2    |    4
 7    |    5
 5    |    6
 3    |    7

The version is 8.2 (no window functions).

create table t (code text, grid integer);
insert into t values
 ('1',1),
 ('1',2),
 ('1',3),
 ('2',4),
 ('2',6),
 ('3',7),
 ('2',5);

score 0 · Accepted Answer · answered Oct 30 '12 at 11:08

This is the solution that worked.

drop sequence if exists s;
create temporary sequence s;
select setval('s', (select max(cast(code as integer)) m from t));

update t
set code = i
from (
    select code, grid, nextval('s') i
    from (
        select code, max(grid) grid
        from t
        group by code
        having count(*) > 1
        order by grid
    ) q
) s
where
    t.code = s.code
    and
    t.grid = s.grid

The problem with it is that the update command must be repeated until there are no more duplicates. It is just a "it is not perfect" problem as it is a one time operation only.

score 0 · Answer 2 · answered Oct 30 '12 at 13:54

Export (and remove) everything but code column (maybe you could subquery for export, and remove just duplicated row). Make code primary with something such auto increment behaviour and reimport everything. code column should be automatically generated.

Renumber duplicates to make them unique

2 Answers2