How to find out the next record id? serial field type, respectively autoincrement

  • why do you need this id? Better use RETURNING - zzashpaupat
  • The question is what for? Here you put learned the following value. In the meantime, someone recognized next to it and put a record there. And what do you do with this knowledge now? - VladimirAbramov

2 answers 2

Pay attention, this is not a supposed id , this is a number that can be used as id (not id !). You can actually get the inserted id calling

 SELECT currval('myshema.tbl_item_id_seq')

in the same session after the insert operation.

Sequences guarantee the receipt of different numbers - in a single transaction, or in parallel, it does not matter. Numbers can be repeated only when the sequence starts the next cycle or it is reset.

If you called

 SELECT nextval('myshema.tbl_item_id_seq')

then when inserting

 INSERT INTO myshema.tbl_item(data,...,more_fields) VALUES('any_data',...,'any data n');

if the field was set as
SERIAL\BIGSERIAL id received by the first function does not match the id actually inserted record.

And it is necessary to indicate

 INSERT INTO myshema.tbl_item(id,data,...,more_fields) VALUES('результат предыдущего вызова nextval', 'any_data',...,'any data n');
  • you can spoof and do so SELECT COALESCE (setval ('techbase.item_id_seq', max (id)), nextval ('techbase.item_id_seq')) FROM techbase.item, (SELECT nextval ('techbase.item_id_seq')) as sel - des1roer
  • I would never do that. Dirty style, fraught with trouble, at least with performance. - oktogen

so get the current id

 SELECT setval('vgok_site.pif_id_seq',nextval('vgok_site.pif_id_seq')-1);