Thread: dump the database data

I have tables create by:

create table address(
    id serial PRIMARY KEY,
    ...);

create table user(
    id serial PRIMARY KEY,
    name text not NULL,
    addressId integer REFERENCES address(id) NOT NULL,
    UNIQUE(name)
);

...

I have used the database for sometime and now I would
like to do a dump and put the data to a sql file. I
used

   pg_dump -d ....

In my dump file I found the insert statements
something like:

    INSERT into user values(5, "George", 1);
    INSERT into user values(6, "Richard", 3);
    INSERT into user values(7, "Pete", 6);

I create the same tables in another database and run
the above insert statement, the data was inserted, but
the seq of user is not updated, it is still pointed to
one. What is wrong?

Thans,

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Fri, Jul 08, 2005 at 09:03:47AM -0700, Tony Smith wrote:
>
>    pg_dump -d ....

What's the complete command?

> In my dump file I found the insert statements
> something like:
>
>     INSERT into user values(5, "George", 1);
>     INSERT into user values(6, "Richard", 3);
>     INSERT into user values(7, "Pete", 6);
>
> I create the same tables in another database and run
> the above insert statement, the data was inserted, but
> the seq of user is not updated, it is still pointed to
> one. What is wrong?

The INSERT statements don't update the sequence because they provide
a value for the serial column (really an integer column), so the
DEFAULT expression (a call to nextval()) isn't used.  But somewhere
in the dump should be a SELECT statement that calls setval() to
update the sequence -- do you not see that?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]

On Fri, Jul 08, 2005 at 09:59:35AM -0700, Tony Smith wrote:
> no, I did not see it. The insert is INSERT into user
> values(5, "George", 1); My dump command is
>
> pg_dump -d databaseName
>
> There maybe more options for username, password. But
> nothing else. Is there any options in the pg_dump
> could fix this?

What version of PostgreSQL are you using?  I just tested 7.2.8,
7.3.10, 7.4.8, 8.0.3, and 8.1devel, and pg_dump from all of them
emitted a setval() to update the sequence.  The line should resemble
one of the following, depending on the version:

SELECT setval ('"user_id_seq"', 7, true);
SELECT pg_catalog.setval('user_id_seq', 7, true);
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('"user"', 'id'), 7, true);

Did you do a visual search or did you use a command like
"grep setval filename"?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/