Backfill default values to a column through migration

Question

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.

@frank-heikens, suggested same thing in question posted earlier.. but seems like MR. max is upset and only want to solve problem in his manner. :) rather than understanding others solutions. — Abhishek Jadav, Dec 12 '22 at 13:52
Thankyou @Beartech. I needed another small suggestion. What should one do if configs already has some default values, and want to just append another one "A" without altering the existing ones. [https://stackoverflow.com/questions/74767586/how-to-write-a-migration-for-backfilling-default-values-to-a-column?noredirect=1#comment131959952_74767586] — Priyanshu0007, Dec 12 '22 at 19:25
A column by nature only contains one value. If you are trying to do something else than you are talking about a specialized column type like `JSON` or `JSONB`. Or storing a string that gets appended to (which is a terrible idea as it goes against the DB normalization) — Beartech, Dec 12 '22 at 19:47
You should head over to https://dba.stackexchange.com to discuss the design of your table as it sounds like you possible have issues with that. — Beartech, Dec 12 '22 at 19:49
@Beartech My column type is `JSONB`. It already has 4 default values that have been changed differently for different Users. I just want to add/append 2 more default values to this column for all Users without changing the existing values of the other 4. Any way this can be done using `update_all`? — Priyanshu0007, Dec 13 '22 at 06:12
If you are still talking about user with blank values for `configs` then I assume it would be something like: `User.where(configs: nil).update_all(configs: {"A": 0})` — Beartech, Dec 13 '22 at 07:10
No not with blank configs values. I got that part. What if `configs` already has some values (Say `"B"`)and I just want to add `"A":0` without changing the existing values of B? — Priyanshu0007, Dec 13 '22 at 07:14
`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 it. Any suggestion or idea how to do that? @Beartech — Priyanshu0007, Dec 13 '22 at 07:22

score 0 · Answer 1 · answered Dec 14 '22 at 03:28

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.

Note that you CAN put this code inside a migration. But you probably shouldn't. Your migration will record the change to the defaults and set the DB to use those defaults. — Beartech, Dec 14 '22 at 03:32

Backfill default values to a column through migration

1 Answers1