How to invoke sequence while inserting new record into postgresql table?

Question

How do I invoke a postgresql sequence while inserting new row into a table?

I want to do something like this:

insert into biz_term(
  biz_term_id, 
  biz_term_name, 
  ) 
values(SELECT nextval(idsequence)',
'temp'
);

I want to do it because when I am trying to insert new record into biz_term table then sequence idsequence is not getting invoked directly. How to invoke it?

score 182 · Accepted Answer · 2013-10-26 07:23:00Z

182

You got it almost. You don't need the SELECT in there:

insert into biz_term(
  biz_term_id, 
  biz_term_name, 
) 
values(
 nextval('idsequence'),
 'temp'
);

Any reasons you did not specify the biz_term_id as serial (or bigserial) which handles that automatically for you?

edited Oct 26, 2013 at 7:23

answered Aug 8, 2012 at 13:01

user330315

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

3 Comments

Ely Fialkoff Over a year ago

To answer your question - I am using this technique since I want my serial to continue counting from one table to another table.

JMess Over a year ago

what is 'idsequence'? Throws "does not exist"

user330315 Over a year ago

@JMess: it's the name of the sequences as used in the question. Obviously if your sequence has a different name, you need to use that name

constantine-d · Accepted Answer · 2021-03-21 21:16:00Z

3

Even though it's a bit old topic, I would like to point out that one good reason to go with BIGINT, against BIGSERIAL is if you are using hibernate. Here is the article: https://vladmihalcea.com/postgresql-serial-column-hibernate-identity/

As the article points out,

Using a SEQUENCE generator is a better alternative since the identifier can be generated prior to executing the INSERT statement

Posting an answer, cause i'm not yet eligible to comment :/ . I apologize in advance in case you find it inappropriate.

edited Mar 21, 2021 at 21:16

user330315

answered Mar 21, 2021 at 19:50

constantine-d

414 bronze badges

4 Comments

user330315 Over a year ago

A bigserial does use a sequence to generate the values.

constantine-d Over a year ago

Yes inded it uses. But the value of a bigserial, will not be availabe to hibernate, because in the case of bigserial, bigserial is generated in the database - thus is not visible to hibernate! Using bigserial in postgres, is the same using identity (auto_increment) regarding hibernate. So, it is recommended to use big int in db, and have a generator in hibernate and use "true" sequences. I have a left a link that it might be better for you to understand what i'm saying.

user330315 Over a year ago

Well Hibernate could simply use nextval() for the sequence behind the serial, but I guess it's too much to ask from an obfuscation layer. Using a (native) sequence is much more efficient, scalable and concurrency safe than anything the obfuscation layer could implement on their own

constantine-d Over a year ago

As far as i know, the native sequence is the one that we have when we have a sequence stored in our database. Please correct me if i'm wrong on this. What i was trying to tell in my previous comments is that, hibernate "prefers the native" ---> and by prefer i mean that hibernate will use nextval() when we use native sequence! :) Hibernate will NOT use nextval() if we use bigserial or serial that is not a native sequence.

Collectives™ on Stack Overflow

How to invoke sequence while inserting new record into postgresql table?

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related