Did you do any research to determine why MAX(id) was greater than NEXTVAL() for the sequence? That would be useful knowledge to add to your post (and, presumably, for you to have).
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.
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
5 comments
[ 2.9 ms ] story [ 19.1 ms ] threadThe 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.
P.S: As I'm not 100% sure, I'm still digging through and I'll update my post if I find anything weird.
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