When inserting a row into a PostgreSQL database table, PHP’s PEAR DB class was giving me the following error:
DB Error: constraint violation
After some research I found out that when we moved the tables to a new database server, the SEQUENCE values were not updated, causing this problem. Sequences are the equivalent of MySQL’s auto-increment and are used to generate a unique identity value for newly inserted rows.
Because our table already had plenty of rows in it and the sequence value was not in sync, the insert would fail because the ID generated for it by the sequence already existed in the database. This is an easy fix, all you have to do is update the sequence value to be back in sync with your data. Simply run the following command in psql to bring everything back into sync:
select setval('your_table_id_seq', (SELECT max(id) FROM your_table) + 1);
Of course you will want to substitute your own sequence, table, and field values into this command. Once you’ve done this everything will be back in sync and you’ll be able to insert new rows into your DB once again.