18

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?

1
  • In PostgreSQL, you don't need to quote column and table names if they're all lowercase.
    – Rodrigo
    Nov 12, 2015 at 13:00

1 Answer 1

23

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:

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:

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.