Re: serial columns & loads misfeature?

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@rogers.com>
PGP Key ID: DB3C29FC