Re: serial columns & loads misfeature?

Kevin Brannen sez:
} I'm new to Postgres, so sorry if this is easy, but I did search the docs
} and could find nothing to answer this...
}
} I've got a Mysql DB that I've dumped out and am trying to insert into a
} Pg DB, as we transition. There were a few changes I had to do to the
} data, but they were easy--except for the auto_increment columns. :-/
}
} 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. 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. I suppose one could set up a trigger/rule to update the
sequence, but that's probably overkill and costly in performance.

} Oh, this on a RH 7.2 system with Pg 7.1.3.
} TIA for any help in understanding this better!
} Kevin
--Greg