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

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