Heidisql: Wrong ID after insert row in postgres database (problem with serial)

[RadekH1]

Steps to reproduce this issue

1. create table with serial primary key
   for example:
   CREATE TABLE test
   (
   id serial NOT NULL,
   test integer,
   PRIMARY KEY (id)
   )
   WITH (
   OIDS=FALSE
   );
2. insert row via editor (autoincrement field is nextval...)
3. do insert 5 times
4. after insert is id not incremented by 1 (step is more one than 1)
   

Current behavior

id is not incement by 1

Expected behavior

id must be increment by 1

Possible solution

in version 9.5.0.5196 is behavior OK

Environment

problem is in great heidisql application

• HeidiSQL version: 9.5.0.5278
  
• Database system and version: postgres 9.4 but it is not depend on version postre database
  
• Operating system: windows
  

[arturm11]

After inserting each row SELECT...WHERE id=nextval() is called. I believe it should be replaced with should be curval().

[arturm11]

Problem occurs also in version 10.0.6005 :-(

[ansgarbecker]

Sorry for the long delay.

@arturm11 How should the SELECT look like instead? I just tested with .. WHERE "id"=curval('user_id_seq'::REGCLASS) like you suggested, but that returns an SQL error.

[eramart]

There is a typo here - should be currval

[arturm11]

Sorry, my mistake, function name is currval. According to documentation:
nextval(regclass) | bigint | Advance sequence and return new value
currval(regclass) | bigint | Return value most recently obtained with nextval for specified sequence
https://www.postgresql.org/docs/current/functions-sequence.html
I am not sure where exactly bug is, anyway it looks that you call nextval multiple times while it should be called only once per each inserted row.

[mavar74]

Maybe this could also help:
_insert into table (col2, col3) VALUES ('c1','c2') returning id;_

id is column where sequence is set as a default value...

https://dba.stackexchange.com/questions/3281/how-do-i-use-currval-in-postgresql-to-get-the-last-inserted-id/3284