Unexpected Error When Inserting a New record
Postgres is a very good relational, SQL, database, but it does not come without it quirks. Some of these will try to make you run away. Here is an interesting error which appeared when I tried to insert a new record into the "user" table of the database.
original: error: duplicate key value violates unique constraint "user_pkey1"
Why is this Error Unexpected?
To my understanding this should have never happened, as the "id" column of the table is set to be an "integer" and an "autoincrement" field. To the readers who are not familiar with the term it means that Posgres should automatically increment the value of the previous record, and generate a new unique identifier value. As a result this kind of error should not be possible to happen.
What did Happen to Cause this Error?
To fix any error one should know what caused it. In my case it was not so obvious and reading through the Internet only made things worse as there were some explanations but not all of them were any good. Until I realized as part of mine seeding the database, I added some records, who did have predefined "ID" values. Apparently this did break the Postgres "counting" function, and it was not able to pickup that the ID numbers have now increased.
How to Actually Fix this Error?
Trying to use the "user_pkey1" sequence listed in the error is absolutely useless. The real sequence is deeply hidden in the database and finding it can be quite difficult by itself. Here is the command you may use to finding the sequence names:
SELECT * FROM information_schema.sequences ORDER by sequence_name;
The above will list the sequences that Postgres has automatically defined in an easier to find alphabetical order. Find your real sequence and lets go ahead.
Lets update the Postgres "counter" for this table
SELECT setval('user_id_seq1', (SELECT max("id") FROM "user") + 1);
This will update the "counter" field with the next expected value
Now we can successfully execute our SQL, and everything will work as expected:
INSERT INTO "user" ("first_name","last_name","email") VALUES ('Tom','Jones','tom@test.com')
Is This a Bug in Posgres?
The question here is if this is something that should be expected or its a bug in Postgres. My rationale is that an autoincrement field should always work as expected. If you need to manually increment "counters" sometimes because you added a manual entry to the database, its a bug. And a pretty nasty bug at that as you will usually find it only when it goes in production.
