CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4rails-migrationsschema-migration
Priyanshu0007
Priyanshu0007

Reputation: 23

Backfill default values to a column through migration

I have a table(Users). In one of its columns(configs) i added a default value ("A"=>0) through a migration. Now all the new users i create have default value of A but the old users don't. I want to backfill the default value of A for the old users using migration. How do I do that?

given:

t.jsonb    "configs",          default: {"B"=>7, "C"=>10, "D"=>10}

This is my existing column. Here B, C and D have different values for different Users. I want to make it into

t.jsonb    "configs",          default: {"B"=>7, "C"=>10, "D"=>10, "A"=>0}

where the values of B, C and D stays the same for all Users but just the default value of "A" gets added to the existing json in the column.

rails - 4.2.11 db - postgres

I have gone through some documentations but couldn't find a comprehensive answer. Any help is appreciated.

Upvotes: -2

Views: 917

Answers (1)

Beartech
Beartech

Reputation: 6411

From your comments is sounds like you want to update a JSONB column to have a new set of defaults, and any existing json hashes get the new key/value pair of "A": 0 added to the current value. A migration can change the DB but you will need to do it programmatically to update the rows that have values already, especially because their values are not all the same. With that said it could be something like:

User.all.each do |u|
  u.configs["A"] = 0
  u.save
end

This will iterate through all of the users and set the value of "a" to zero. If no "a" exists in the hash it will add it with the value of zero without touching anything else in the JSON. If "a" already exists for a user it will be set to zero. So if you have users whose value for "a" has already changed from the default of zero you can avoid them with:

User.all.each do |u|
  unless conifigs["A"]  # if "A" already exists skip this
    u.configs["A"] = 0
    u.save
  end
end

Please read https://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails for information on how to leverage JSONB in Rails. It is a very powerful tool if you put in the code to really get the most use out of it. Be sure and read the part about store_accessor, it would help you to do a lot more with that JSONB column.

Upvotes: 0

Related Questions