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

Re: serial columns & loads misfeature?

From: nconway(at)klamath(dot)dyndns(dot)org (Neil Conway)
To: Gregory Seidman <gss+pg(at)cs(dot)brown(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: serial columns & loads misfeature?
Date: 2002-06-28 19:21:21
Message-ID: 20020628192121.GA5727@klamath.dyndns.org (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, Jun 28, 2002 at 02:50:46PM -0400, Gregory Seidman wrote:
> Kevin Brannen sez:
> } After I created the DB, I inserted the data (thousands of inserts) via 
> } psql.  All went well.  Then I started testing the changed code (Perl) 
> } and when I went to insert, I got a "dup key" error.
> [...]
> } and things will be fine from here after, but surely this is a common 
> } enough problem after a bulk load that there is something already built 
> } in to handle this and I just don't have it configured correctly (or is 
> } this a bug?).
> 
> It's a known problem.

IMHO, it's not a problem. If you abuse sequences (i.e. INSERT into a
SERIAL column a value that is not generated by its sequence), you're
going to run into trouble -- so the easy solution is, "don't do that".

> I ran into the exact same thing (also transferring
> from MySQL to PostgreSQL). The right way to do it is to add a line after
> all the inserts for the table (I am assuming you have a big SQL file dumped
> by mysql or whatever):
> 
> SELECT setval('seq_name', max(serial_column)) FROM appropriate_table;
> 
> Unfortunately, I don't think even pg_dump produces this line, though I
> could be wrong.

You're wrong -- pg_dump will setval() the sequence to the value it had
when the dump was made.

Cheers,

Neil

-- 
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC



In response to

pgsql-general by date

Next:From: Jason EarlDate: 2002-06-28 19:21:30
Subject: Re: serial columns & loads misfeature?
Previous:From: Tom LaneDate: 2002-06-28 19:11:35
Subject: Re: serial columns & loads misfeature?

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