Sign up ×
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
In PostgreSQL, you don't need to quote column and table names if they're all lowercase. – Rodrigo Nov 12 at 13:00

1 Answer 1

up vote 10 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.

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:

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, 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:

share|improve this answer
Thanks for the advice! Worked very well. – Valentin Despa Mar 13 '14 at 9:35
Isn't text slower than varchar(50)? – Rodrigo Nov 12 at 13:01
@Rodrigo: Not in Postgres. There is a link above to more explanation: Or here. – Erwin Brandstetter Nov 14 at 1:12
The last test here <>; convinced me that varchar(n) is faster for most fields where a restriction in size is convenient (people names, emails, addresses, species names, etc). Text is faster (or the same) if you won't check the length, it seems. – Rodrigo Nov 18 at 13:53

Your Answer


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.