Re: Serials: removing the holes? (consecutive)

On Sat, 03 Aug 2002 10:44:31 +0900
Jean-Christian Imbeault <jc@mega-bucks.co.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