Re: Serials: removing the holes? (consecutive)

On Sat, 03 Aug 2002 10:44:31 +0900
Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp> wrote:

> Serials are a great datatype but when using them you sometimes get
> non-consecutive numbers. I understand some of the reasons for this.
>
> Is there a (simple) way to re-sequence a serial column that is the
> primary key for a table so that the numbers are all consecutive?
>
> I don't mnind having holes but once in a while I'd like to re-sequence
> the table so that the current numbes in use become consecutive.

How about a transaction using a temp. sequence. I think it's not so difficult.
Actually, all you have to do is to rename the arguments(a and tbl) in the
transaction and setval().

-- to create two tables as an example
CREATE SEQUENCE seq_p_key;
CREATE TABLE tbl (a int4 PRIMARY KEY DEFAULT nextval('seq_p_key'), b text);
INSERT INTO tbl(b) VALUES('d');
INSERT INTO tbl(b) VALUES('i');
INSERT INTO tbl(b) VALUES('p');
INSERT INTO tbl(b) VALUES('r');
CREATE TABLE tbl2 (a int4 CONSTRAINT cnt_tbl_a REFERENCES tbl(a)
ON DELETE CASCADE ON UPDATE CASCADE
NOT DEFERRABLE,
c text);
INSERT INTO tbl2 VALUES(1, 'delete');
INSERT INTO tbl2 VALUES(2, 'insert');
UPDATE tbl SET a = 10 WHERE a = 2;
UPDATE tbl SET a = 2 WHERE a = 3;

-- outputs of the results of initial INSERT/UPDATE
SELECT * FROM tbl;
a | b
----+---
1 | d
4 | r
10 | g
2 | p
(4 rows)
SELECT * FROM tbl2;
a | c
----+--------
1 | delete
10 | insert
(2 rows)

-----------------------------------------------------
-- to eliminate gaps of the sequence on the primary key
-- a: target column having primary key
-- tbl:target table having target column
BEGIN;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
CREATE TEMP SEQUENCE seq_n;
UPDATE tbl SET a = t1.i
FROM (SELECT t0.*, nextval('seq_n') AS i
FROM (SELECT * FROM tbl ORDER BY a) AS t0
LIMIT ALL ) AS t1
WHERE t1.a <> t1.i
AND tbl.a = t1.a;
DROP SEQUENCE seq_n;
END;
-- to adjust the sequence to the number of the rows
-- note: if the transaction fails, don't execute next query.
SELECT setval('seq_p_key', (SELECT COUNT(*) FROM tbl));
-------------------------------------------------------

-- outputs of the result after re-createing a primary key.
SELECT * FROM tbl;
a | b
---+---
1 | d
2 | p
3 | r
4 | g
(4 rows)
SELECT * FROM tbl2;
a | c
---+--------
1 | delete
4 | insert
(2 rows)

Regards,
Masaru Sugawara