Hi there,
I would like to reset a sequence so its id will start with 1 if I insert
a new record into the table, after I have deleted all records from the
table.
I am using Postgres 8.03 and here is what I tried:
test=# create table tblperson (
test(# id SERIAL NOT NULL,
test(# name VARCHAR(200)
test(# );
NOTICE: CREATE TABLE will create implicit sequence "tblperson_id_seq"
for serial column "tblperson.id"
CREATE TABLE
test=# INSERT INTO tblperson (name) VALUES ('John Phelps');
INSERT 27562 1
test=# SELECT * from tblperson;
id | name
----+-------------
1 | John Phelps
(1 row)
test=# SELECT * from tblperson_id_seq;
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
------------------+------------+--------------+---------------------+
tblperson_id_seq | 1 | 1 | 9223372036854775807 |
1 | 1 | 0 | f | t
(1 row)
Then, I truncated the table in order to delete all records and insert a
new record to see if it's id will start with 1 - but it starts with 2.
test=# TRUNCATE tblperson;
TRUNCATE TABLE
test=# INSERT INTO tblperson (name) VALUES ('John Phelps');
INSERT 27564 1
test=# SELECT * from tblperson;
id | name
----+-------------
2 | John Phelps
(1 row)
After I truncated tblperson I supposed that the Id will start with 1
again if I insert a new record into tblperson. I thought, truncating the
table tblperson will also reset its sequence "tblperson_id_seq"!? Am I
wrong?
After that, I tried to set the sequence back to 1 since I cannot set the
sequence to 0 using setval() (error: value 0 is out of bounds for
sequence). Unfortunately, setting the sequence back to 1 will start with
id = 2
test=# SELECT setval('tblperson_id_seq', 0);
ERROR: setval: value 0 is out of bounds for sequence "tblperson_id_seq"
(1..9223372036854775807)
test=# SELECT setval('tblperson_id_seq', 1);
setval
--------
1
(1 row)
test=# INSERT INTO tblperson (name) VALUES ('John Phelps');
INSERT 27566 1
test=# SELECT * from tblperson;
id | name
----+-------------
2 | John Phelps
(1 row)
I could do the following, but I don't know if this is a clean solution:
TRUNCATE tblperson;
SELECT setval('tblperson_id_seq', 1);
INSERT INTO tblperson (name) VALUES ('test1');
INSERT INTO tblperson (name) VALUES ('test2');
INSERT INTO tblperson (name) VALUES ('test3');
UPDATE tblperson set id = id-1;
test=# SELECT * from tblperson;
id | name
----+-------
2 | test2
3 | test3
1 | test
(3 rows)
Any idea, how I can reset the sequence so it will start with 1 again?
Many thanks in advance,
Nico
