Postgresql nextval generating existing values

Question

I had to migrate from mySql to Ruby to a rails application using postgresql. There is no problem but one, and I do not know how to solve it.

The data transfer brought identifiers along with it, and postgresql now has problems with existing identifiers: it is not clear to me where it gets the value that it uses to determine the base for nextval: this, of course, is not the highest value in the column, although you might think That would be a good idea. In any case, it now encounters existing id values. id created from standard RoR migration is defined as

not null default nextval('geopoints_id_seq'::regclass) 

Is it possible to hack some place which value that it uses as a base? Now this problem can occur in any of the 20 or so tables: I could use

 'select max(id) from <table_name>' 

but this seems to make the idea of ​​an auto-increment column pointless.

How is this best handled?

Answer 1

Postgres adapter has reset_pk_sequences! . You can call it and it will set it to max (id) + 1, which is probably what you need.

In some projects, I get ETL'ed data often enough to justify the rake task, to do this for all models or for a specific model. Here the task is to include it in some Rakefile or in it yourself under lib / tasks:

 desc "Reset all sequences. Run after data imports" task :reset_sequences, :model_class, :needs => :environment do |t, args| if args[:model_class] classes = Array(eval args[:model_class]) else puts "using all defined active_record models" classes = [] Dir.glob(RAILS_ROOT + '/app/models/**/*.rb').each { |file| require file } Object.subclasses_of(ActiveRecord::Base).select { |c| c.base_class == c}.sort_by(&:name).each do |klass| classes << klass end end classes.each do |klass| next if klass == CGI::Session::ActiveRecordStore::Session && ActionController::Base.session_store.to_s !~ /ActiveRecordStore/ puts "reseting sequence on #{klass.table_name}" ActiveRecord::Base.connection.reset_pk_sequence!(klass.table_name) end end 

Now you can run this either for all models (defined in RAIS_ROOT / app / models) using rake reset_sequences , or for a specific model by passing the class name.

Answer 2

The rails 3 version looks like this:

 namespace :db do desc "Reset all sequences. Run after data imports" task :reset_sequences, :model_class, :needs => :environment do |t, args| if args[:model_class] classes = Array(eval args[:model_class]) else puts "using all defined active_record models" classes = [] Dir.glob(RAILS_ROOT + '/app/models/**/*.rb').each { |file| require file } ActiveRecord::Base.subclasses.select { |c|c.base_class == c}.sort_by(&:name).each do |klass| classes << klass end end classes.each do |klass| puts "reseting sequence on #{klass.table_name}" ActiveRecord::Base.connection.reset_pk_sequence!(klass.table_name) end end end 

https://gist.github.com/909032

Answer 3

with this definition, the column will receive the next value from the sequence geointoints_id_seq. This sequence is not bound directly to the table. If you are transferring data, you need to create or update this sequence so that its starting point is greater than the current maximum id in your table.

You should be able to set a new value, for example,

  ALTER SEQUENCE geopoints_id_seq RESTART with 1692; 

Or regardless of the selected max (id) from table_name; exits

Answer 4

PG uses the sequence:

Make the current value of 1 higher than the maximum value in your table like this.

 SELECT setval('geopoints_id_seq', 999999999, true); 

Also see these

http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL

http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html

Answer 5

Use setval () to set the initial value for the sequence.