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

Question

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?

Answer 1

Nothing is wrong with your table definition.
_{(Except that I would use jos_content_id or something instead of the non-descriptive column name id.
And I probably would use text instead of varchar(50).}

Your INSERT statement is the problem.

With your id column defined as serial, you shouldn't insert manual values for id. Those may collide with the next value from the associated sequence.

Provide an explicit list of target columns (which is almost always a good idea for persisted INSERT statements) and omit serial columns completely.

INSERT INTO jos_content(asset_id, some_column, ...)
VALUES (36,'About',...);

If you need the value(s) of automatically generated column(s) immediately, use the RETURNING clause:

INSERT ...
RETURNING id;  -- possibly more

More details in this related answer on SO:

• Increment value of a table ID for each INSERT

If you have manual entries in serial columns that might conflict later, set your sequence to the current maximum id to fix this once:

SELECT setval('jos_content_id_seq', max(id))
FROM   jos_content;

Where jos_content_id_seq is the default name for a sequence owned by jos_content.id, which you already found in the column default. Seems to be xhzt8_content_id_seq in your case;

---

Update: A similar issue popped up on SO and I came up with a new solution:

• Make Postgres choose the next minimal available id