PostgreSQL nextval生成现有值 [英] postgresql nextval generating existing values

问题描述

我不得不从基于Rails应用程序的基于mySql的ruby迁移到使用postgresql。到目前为止没有任何问题，我不知道如何解决。

I had to migrate from a mySql based ruby on rails app to using postgresql. No problems but one so far, and I don't know how to solve it.

数据的迁移带来了id，而postgresql现在存在现有id的问题：我不清楚它从何处获得用于确定值的值nextval的基础：尽管您可能认为这是一个好主意，但它当然不是该列中的最高值。无论如何，它现在都在与现有的id值冲突。通过标准RoR迁移创建的id列定义为

The migration of data brought ids along with it, and postgresql is now having problems with existing ids: it's not clear to me where it gets the value that it uses to determine the base for nextval: it certainly isn't the highest value in the column, although you might think that would be a good idea. In any case, it's now colliding with existing id values. id column, created from a standard RoR migration is defined as

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

是否存在某个地方可以破解其用作基础的值？现在，大约20个表中的任何一个都可能出现此问题：我可以使用

Is there some place that the value it uses as a base can be hacked? This problem could now arise in any of 20 or so tables: I could use

'select max(id) from <table_name>' 

但这似乎使自动递增列的想法毫无意义。

but that seems to make the idea of an autoincrement column pointless.

如何最好地处理？

推荐答案

有一个 reset_pk_sequences 方法> Postgres适配器。您可以调用它，它将其设置为max（id）+ 1，这可能是您想要的。

There is a reset_pk_sequences! method on the Postgres adapter. You can call it and it will set it to max(id) + 1, which is probably what you want.

在某些项目中，我经常获得数据ETL。足以保证要为所有模型或指定模型执行此任务。这是任务-将其包含在某些Rakefile中或包含在lib / tasks中：

In some projects I get data ETL'ed in often enough to warrant a rake task to do this for all models, or for a specified model. Here's the task - include it in some Rakefile or in it's own 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

现在您可以为所有模型运​​行此命令（使用 rake reset_sequences 在RAIS_ROOT / app / models下定义，或通过传递类名来输入特定模型。

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

这篇关于PostgreSQL nextval生成现有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！