Skip site navigation (1) Skip section navigation (2)

pg_dump & restore question regarding creating with serial

From: Pam Wampler <Pam_Wampler(at)taylorwhite(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: pg_dump & restore question regarding creating with serial
Date: 2002-10-04 20:50:48
Message-ID: 2E4528861499D41199D200A0C9B15BC001D50673@FRISTX (view raw or flat)
Thread:
Lists: pgsql-novice

Josh
thanks for replying but please let me clarify:

my original pg_dump has records with the id from 1 to 644714794.
my "replica" database started at 4064868  (where did that number come from?
--
there is not an id of 1 in the database) 
I am rerunning it now -- to see if it happens again
when I run select * from inv_trans_seq_id
i see this:
mcs=# select * from inv_trans_id_seq;
  sequence_name   | last_value | increment_by |      max_value      |
min_value | cache_value | log_cnt | is_cycled | is_called
------------------+------------+--------------+---------------------+-------
----+-------------+---------+-----------+-----------
 inv_trans_id_seq |          1 |            1 | 9223372036854775807 |
1 |           1 |       1 | f         | f
(1 row)

which leads me to believe that the first record will have 1 as it's id

what I saw in the first try was 4064868????


I understand what is going on in sequences -- but I don't understand what I
am seeing.

thanks

Pam
-----Original Message-----
From: Josh Berkus [mailto:josh(at)agliodbs(dot)com]
Sent: Friday, October 04, 2002 3:14 PM
To: Pam Wampler; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] pg_dump & restore question regarding creating with
serial



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(at)agliodbs(dot)com
Aglio Database Solutions
San Francisco

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-10-04 22:42:24
Subject: Re: pg_dump & restore question regarding creating with seri al
Previous:From: Josh BerkusDate: 2002-10-04 19:14:23
Subject: Re: pg_dump & restore question regarding creating with serial

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group