I have a table which is created this way:
-- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) DEFAULT '' NOT NULL, PRIMARY KEY ("id") );
Later some rows are inserted specifying the id:
INSERT INTO "jos_content" VALUES (1,36,'About',...)
At a later point some records are inserted without id and they fail with the error:
Error: duplicate key value violates unique constraint.
Apparently the id got defined as a sequence:
Each failed insert increases the pointer in the sequence till it increments to a value that no longer exists and the queries succeed.
What is wrong with the table definition? What is the smart way to fix this?