Duplicate key violates unique constraint

I see this every once and a while with Oracle when a production database is exported and imported into another environment without using CONSISTENT=Y (with the old exp command) or without using a FLASHBACK_TIME with the newer expdp command.

The problem is that sequences are exported first so if any more rows are inserted into tables using those sequences during the export process the tables and sequences in the export will be out of sync. Adding CONSISTENT=Y or a FLASHBACK_TIME allows you to export from a single point in time.

Edit: Since the OP was regarding PostgreSQL (which I'm unfamiliar with) I did a quick google search that landed me on the pg_dump documentation. According to the document exports should be consistent. http://www.postgresql.org/docs/9.0/static/app-pgdump.html

The most common cause of this is doing a data import, etc. and not setting the starting sequence number to be one higher than that table's current max(id) - something I found out after getting the same error with a MySQL to Postgres migration I'm working on.

The terminology is a little different with Postgres, but nearly every database has a way of setting what you'd like the initial value of the autoincrementing id field to be.

At this point I'm pretty certain that the error was due to data import, and I will add it to my post. Thanks for the suggestion!

P.S: As I'm not 100% sure, I'm still digging through and I'll update my post if I find anything weird.

agreed, that would be more beneficial than the actual fix - this could potentially just paper over the underlying problem.