Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

Nothing is wrong with your table definition.
(Except hat 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.

Just omit serial columns completely from your inserts. Remember to provide a column definition list (which is almost always a good idea):

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

If you need the automatically generated id immediately, use the RETURNING clause to get it back from the INSERT:

INSERT ...
RETURNING id;

More details in this related answer on SO:
Increment value of a table ID for each INSERT

If you already have manual entries 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 have come up with a new solution:
Autoincrement, but omit existing values in the column

share|improve this answer
    
Thanks for the advice! Worked very well. –  Valentin Despa Mar 13 at 9:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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