Tweeted twitter.com/#!/StackDBAs/status/443925297566859264
2 adapt tags
| link
1
source | link

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

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:

enter image description here

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?