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.
SELECT nextval('jos_content_id_seq'::regclass)
What is wrong with the table definition? What is the smart way to fix this?