CODEWITHSUNDEEP
sqlpostgresqlauto-increment
WinSupp
WinSupp

Reputation: 371

Auto increment issues postgresql

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

Upvotes: 3

Views: 3099

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324265

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.

Upvotes: 3

Related Questions