How to reset a sequence so it will start with 1 again?

[Nico Grubert <nicogrubert@xxxxxxxxx>]

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