Moved from MySQL to PostGres
Posted: Sun Feb 09, 2014 11:14 pm
Due to a huge amount of entires, MYSQL 5.1 was choking so I had to resort to migrating to Postgres 8.1 .
All the entries migrated but the administrator cannot post to the s9y blog.
What do I need to look for?
Further the serverlog has
ERROR: duplicate key violates unique constraint "serendipity_entries_pkey"
and
ERROR: duplicate key violates unique constraint "serendipity_referrers_pkey"
Found solution for entries at
http://hcmc.uvic.ca/blogs/index.php?blo ... &tb=1&pb=1
How to fix PostgreSQL error "duplicate key violates unique constraint"
20/04/11
Permalink 10:29:54 am, by jamie, 169 words, 4429 views English (CA)
Categories: Notes; Mins. worked: 0
How to fix PostgreSQL error "duplicate key violates unique constraint"
If you get this message when trying to insert data into a PostgreSQL database:
ERROR: duplicate key violates unique constraint
That likely means that the primary key sequence in the table you're working with has somehow become out of sync, likely because of a mass import process (or something along those lines). Call it a "bug by design", but it seems that you have to manually reset the a primary key index after restoring from a dump file. At any rate, to see if your values are out of sync, run these two commands:
SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval('the_primary_key_sequence');
If the first value is higher than the second value, your sequence is out of sync. Back up your PG database (just in case), then run thisL
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);
That will set the sequence to the next available value that's higher than any existing primary key in the sequence.
All the entries migrated but the administrator cannot post to the s9y blog.
What do I need to look for?
Further the serverlog has
ERROR: duplicate key violates unique constraint "serendipity_entries_pkey"
and
ERROR: duplicate key violates unique constraint "serendipity_referrers_pkey"
Found solution for entries at
http://hcmc.uvic.ca/blogs/index.php?blo ... &tb=1&pb=1
How to fix PostgreSQL error "duplicate key violates unique constraint"
20/04/11
Permalink 10:29:54 am, by jamie, 169 words, 4429 views English (CA)
Categories: Notes; Mins. worked: 0
How to fix PostgreSQL error "duplicate key violates unique constraint"
If you get this message when trying to insert data into a PostgreSQL database:
ERROR: duplicate key violates unique constraint
That likely means that the primary key sequence in the table you're working with has somehow become out of sync, likely because of a mass import process (or something along those lines). Call it a "bug by design", but it seems that you have to manually reset the a primary key index after restoring from a dump file. At any rate, to see if your values are out of sync, run these two commands:
SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval('the_primary_key_sequence');
If the first value is higher than the second value, your sequence is out of sync. Back up your PG database (just in case), then run thisL
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);
That will set the sequence to the next available value that's higher than any existing primary key in the sequence.