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

Question

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.

Answer 1

Assuming your ids are generated from a bignum sequence, just RESTART the sequence and update the table with idcolumn = DEFAULT.

CAVEAT: If this id column is used as a foreign key by other tables, make sure you have the on update cascade modifier turned on.

For example:

Create the table, put some data in, and remove a middle value:

db=# create sequence xseq;
CREATE SEQUENCE
db=# create table foo ( id bigint default nextval('xseq') not null, data text );
CREATE TABLE
db=# insert into foo (data) values ('hello'), ('world'), ('how'), ('are'), ('you');
INSERT 0 5
db=# delete from foo where data = 'how';
DELETE 1
db=# select * from foo;
 id | data  
----+-------
  1 | hello
  2 | world
  4 | are
  5 | you
(4 rows)

Reset your sequence:

db=# ALTER SEQUENCE xseq RESTART;
ALTER SEQUENCE

Update your data:

db=# update foo set id = DEFAULT;
UPDATE 4
db=# select * from foo;
 id | data  
----+-------
  1 | hello
  2 | world
  3 | are
  4 | you
(4 rows)

Answer 2

The question is old, but we got a new question from a desperate user on dba.SE after trying to apply what is suggested here. Find an answer with more details and explanation over there:

• Compacting a sequence in PostgreSQL

The currently accepted answer will fail for most cases.

• Typically, you have a PRIMARY KEY or UNIQUE constraint on an id column, which is NOT DEFERRABLE by default. (OP mentions references and constraints.) Such constraints are checked after each row, so you most likely get unique violation errors trying. Details:

  • Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

• Typically, one wants to retain the original order of rows while closing gaps. But the order in which rows are updated is arbitrary, leading to arbitrary numbers. The demonstrated example seems to retain the original sequence because physical storage still coincides with the desired order (inserted rows in desired order just a moment earlier), which is almost never the case in real world applications and completely unreliable.

The matter is more complicated than it might seem at first. One solution (among others) if you can afford to remove the PK / UNIQUE constraint (and related FK constraints) temporarily:

BEGIN;

LOCK tbl;

-- remove all FK constraints to the column

ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;  -- remove PK

-- for the simple case without FK references - or see below:    
UPDATE tbl t  -- intermediate unique violations are ignored now
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

-- Update referencing value in FK columns at the same time (if any)

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY(id); -- add PK back

-- add all FK constraints to the column back

COMMIT;

This is also much faster for big tables, because checking PK (and FK) constraint(s) for every row costs a lot more than removing the constraint(s) and adding it (them) back.

If there are FK columns in other tables referencing tbl.id, use data-modifying CTEs to update all of them.

Example for a table fk_tbl and a FK column fk_id:

WITH u1 AS (
   UPDATE tbl t
   SET    id = t1.new_id
   FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
   WHERE  t.id = t1.id
   RETURNING t.id, t1.new_id  -- return old and new ID
   )
UPDATE fk_tbl f
SET    fk_id = u1.new_id      -- set to new ID
FROM   u1
WHERE  f.fk_id = u1.id;       -- match on old ID

More in the referenced answer on dba.SE.