Retrieve the nextval from a sequence using activerecord in Ruby on Rails 3.2.14 / Ruby 2.0.0 / PostgreSQL 9.2.4

Question

This should be SO simple. I want to retrieve the nextval of a sequence... it's not a default value... it's not a primary key... it's not a foreign key. In rare cases, I need a sequence number for a user supplied value.

I've tried the following:

@nextid = ActiveRecord::Base.connection.execute("SELECT nextval('xscrpt_id_seq')")

and what I get back is:

#<PG::Result:0x007fe668a854e8 @connection=#<PG::Connection:0x00000003aeff30>>

And by using

@nextid[0]["nextval"]

I can get the correct value, but it doesn't seem like the right way to approach the problem. I've searched, I read "Pro Active Record", which said to use:

M_script.find_by_sql("SELECT nextval('xscript_id_seq')")

but, that didn't work.

Any hints on the "correct" (Rails way) to retrieve a nextval from a sequence in ROR, would be very appreciated!

Itay Grudev · Accepted Answer · 2021-02-22 11:26:42Z

12

I believe:

ActiveRecord::Base.connection.execute( "SELECT nextval('xscrpt_id_seq')" )

is the correct solution. As I said in my original question, I checked the book "Pro Active Record" and it is their recommended solution. I'm still not exactly sure how ActiveRecord is making the connection, or if there's any maintenance I need to do (such as closing it).

ActiveRecord used to have a next_sequence_number method, but that has been deprecated.

edited Feb 22, 2021 at 11:26

Itay Grudev

7,5544 gold badges60 silver badges97 bronze badges

answered Jul 31, 2013 at 15:04

Ron

4573 silver badges13 bronze badges

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

Comments

kbrock · Accepted Answer · 2016-01-21 20:30:02Z

11

You probably want to use select_value and sequence_name:

Xscrpt.connection.select_value("select nextval('#{Xscrpt.sequence_name}')").to_i

answered Jan 21, 2016 at 20:30

kbrock

1,02713 silver badges18 bronze badges

2 Comments

Jagdish Narayandasani Over a year ago

select_value function is super helpful and no need to fetch like [0]['nextval']. Thanks @kbrock

Jake Over a year ago

You don't need the #to_i at the end, #select_value casts the value to Integer for you.

Bacho sh · Accepted Answer · 2018-05-15 08:37:00Z

1

This works too:

 @seq = Smodelname.find_by_sql(" select seq_name.nextval as id from dual ")[0][:id]

answered May 15, 2018 at 8:37

Bacho sh

566 bronze badges

Comments

Gernot Kogler · Accepted Answer · 2021-06-05 06:22:31Z

1

At least with Rails 5.1.7, be careful:

ActiveRecord::Base.connection.select_value

increments the sequence within a transaction only for the first call. When you execute it multiple times, you'll get always the same value and the sequence is not incremented.

ActiveRecord::Base.connection.execute

does not cache and works as expected

answered Jun 5, 2021 at 6:22

Gernot Kogler

111 bronze badge

Collectives™ on Stack Overflow

Retrieve the nextval from a sequence using activerecord in Ruby on Rails 3.2.14 / Ruby 2.0.0 / PostgreSQL 9.2.4

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related