Thread: pg_dump & restore question regarding creating with serial

Hi
I am trying to make a copy of one database to another.  I have done the
pg_dump and then psql -f filename into new database-- but when I look at a
table that has been created with (id serial primary key) -- -- on the new
database the id does not start at 1 but the number greater than the last id
of the original table.  How can I make the id start at 1  -- I would have
thought that the initial load into the new database would be an exact
replica of the original database?

When I do \d for the table I see this:

 id           | integer                     | not null default
nextval('"inv_trans_id_seq"'::text)

thanks

Pam Wampler

Pam,

> I am trying to make a copy of one database to another.  I have done the
> pg_dump and then psql -f filename into new database-- but when I look at a
> table that has been created with (id serial primary key) -- -- on the new
> database the id does not start at 1 but the number greater than the last id
> of the original table.  How can I make the id start at 1  -- I would have
> thought that the initial load into the new database would be an exact
> replica of the original database?

It *is* an exact replica ... including having the sequence for the table be at
whatever number the original database is at.  This is intentional and desired
behaviour.

>  id           | integer                     | not null default
> nextval('"inv_trans_id_seq"'::text)

After rebuilding the database, run:
SELECT SETVAL('inv_trans_id_seq', 1);

HOWEVER, I think you need some comprehension of Postgres sequences.
Fortunately, Justin has created a excellent animated flash tutorial on
sequences; see:
http://techdocs.postgresql.org/college/001_sequences/index.php

--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco