Compact or renumber IDs for all tables, and reset sequences to max(id)?

user_217071 0
Viewed 6480

After running for a long time, I get more and more holes in the id field. Some tables' id are int32, and the id sequence is reaching its maximum value. Some of the Java sources are read-only, so I cannot simply change the id column type from int32 to long, which would break the API.

I'd like to renumber them all. This may be not good practice, but good or bad is not concerned in this question. I want to renumber, especially, those very long IDs like "61789238", "548273826529524324". I don't know why they are so long, but shorter IDs are also easier to handle manually.

But it's not easy to compact IDs by hand because of references and constraints.

Does PostgreSQL itself support of ID renumbering? Or is there any plugin or maintaining utility for this job?

Maybe I can write some stored procedures? That would be very nice so I can schedule it once a year.

compact-database auto-increment identity postgresql
5 Answers

Since I didn't like the answers, I wrote a function in PL/pgSQL to do the job. It is called like this :

=> SELECT resequence('port','id','port_id_seq');
 resequence   
--------------
 5090 -> 3919

Takes 3 parameters

  1. name of table
  2. name of column that is SERIAL
  3. name of sequence that the SERIAL uses

The function returns a short report of what it has done, with the previous value of the sequence and the new value.

The function LOOPs over the table ORDERed by the named column and makes an UPDATE for each row. Then sets the new value for the sequence. That's it.

  1. The order of the values is preserved.
  2. No ADDing and DROPing of temporary columns or tables involved.
  3. No DROPing and ADDing of constraints and foreign keys needed.
  4. Of course You better have ON UPDATE CASCADE for those foreign keys.

The code :

CREATE OR REPLACE FUNCTION resequence(_tbl TEXT, _clm TEXT, _seq TEXT) RETURNS TEXT AS $FUNC$
DECLARE                                            
        _old BIGINT;_new BIGINT := 0;              
BEGIN
        FOR _old IN EXECUTE 'SELECT '||_clm||' FROM '||_tbl||' ORDER BY '||_clm LOOP
                _new=_new+1;
                EXECUTE 'UPDATE '||_tbl||' SET '||_clm||'='||_new||' WHERE '||_clm||'='||_old;
        END LOOP;
        RETURN (nextval(_seq::regclass)-1)||' -> '||setval(_seq::regclass,_new);
END $FUNC$ LANGUAGE plpgsql;
user_124535960
Related