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. — user330315
– user330315, Commented Jun 10, 2014 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
– WinSupp, Commented Jun 10, 2014 at 20:06
possible duplicate of Autoincrement, but omit existing values in the column — Daniel Vérité
– Daniel Vérité, Commented Jun 10, 2014 at 21:24
Don't fight the symptoms, fix the problem: change your code to either use nextval() or no value at all — user330315
– user330315, Commented Jun 10, 2014 at 22:03

Craig Ringer · Accepted Answer · 2014-06-10 23:40:57Z

3

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.

answered Jun 10, 2014 at 23:40

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Auto increment issues postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related