Auto increment issues postgresql

Question

Facing some issues with the auto-increment property in postgresql

I created a table say emp

create table emp
( empid serial,
empname varcha(50),
primary key (empid)
);

I inserted one value with empid as blank:

insert into emp (empname) values ('test1');

Next insert by specifying the empid value:

insert into emp (empid,empname) values (2,'test2');

Now, the next time if I insert a value without specifying the empid value, it will give an error because it will try to insert the empid as 2:

insert into emp (empname) values ('test3');

ERROR: duplicate key value violates unique constraint "emp_pkey"
DETAIL: Key (empid)=(2) already exists.

Can someone help me with a workaround for this issue so that with or without specifying a value, the autoincrement should pick up the max(value) +1 ??

Thanks

This is not how sequences work. If you want them to work automatically, just don't specify a value for the column. — , Jun 10 '14 at 19:49
Is there some work around to it? The problem is that I have some inserts with the empid values in the program which can not be taken away... so just wanted to check if there is a way to resolve this error? — WinSupp, Jun 10 '14 at 20:06
possible duplicate of [Autoincrement, but omit existing values in the column](http://stackoverflow.com/questions/22407444/autoincrement-but-omit-existing-values-in-the-column) — Daniel Vérité, Jun 10 '14 at 21:24
Don't fight the symptoms, fix the problem: change your code to either use `nextval()` or no value at all — , Jun 10 '14 at 22:03

score 3 · Answer 1 · answered Jun 10 '14 at 23:40

You can't cleanly mix use of sequences and fixed IDs. Inserting values without using the sequence won't update the sequence, so you'll get collisions.

If you're doing your manual insertions in a bulk load phase or something you can:

BEGIN
LOCK TABLE the_table IN ACCESS EXCLUSIVE MODE
Do your INSERTs
SELECT setval('seq_name', 14), replacing 14 with the new sequence value. This can be a subquery against the table.
COMMIT

... but in general, it's better to just avoid mixing sequence ID generation and manually assigned IDs.

Auto increment issues postgresql

1 Answers1