I had an interesting circumstance happen with Postgres (PostgreSQL) and I want to remember how I fixed the problem.
Somehow I had four entries in a database table, but the key for that table thought that there were only two entries. I don't yet know how this happened, but at the time I didn't care, I just needed to get past the problem. The details of the problem, and the fix, follow.
In my case the table name was
carrier, and this table and the key were created like this:
CREATE TABLE carrier ( carrier_id SERIAL NOT NULL UNIQUE, carrier_name VARCHAR(100) NOT NULL, PRIMARY KEY(carrier_id) ); CREATE UNIQUE INDEX carrier_pk ON carrier (carrier_id);
I found that a
select count(*) from carrier told me that I had four rows in the
carrier table, but when I checked the value of the counter in
carrier_pk it thought that there were only two records in the table. This made it a problem whenever I tried to insert another carrier entry; I kept getting this error message:
Cannot insert a duplicate key into unique index
This made it impossible to insert new data records into the table.
After a little while I found a workaround. Running the following command, I was able to tell the
carrier_pk index that there were really 4 entries in the table already:
Once I did this I was able to insert new data into the
Before trying this, I tried using an
UPDATE statement, and several other things, but this turned out to be the key. I don't remember exactly where I found this answer, but I started as usual at google.
I still don't know what caused the problem originally, but at least I found a way out of the problem.