Geek Answers Handbook

Add a NOT NULL field with no default to the populated database

I have a table, name it MyTable . It is part of the Postgresql database.

MyTable lot of entries, say, over a million. I would like to add a field to this table, name it MyNewField . It must be added using ActiveRecord migration.

This field must be without default values ​​and not equal to NULL. As a result, the migration class in it will be something like this:

 class AddMyFieldToMyTable < ActiveRecord::Migration def change add_column :my_table, :my_field, :text, null: false end end

However, it will throw an error (PG :: NotNullViolation) because the table already contains rows, all of which will have MyField set to NULL.

I would like to do the following: add a line without a default value and set the NULL value to false (without starting PG :: NotNullViolation). Then paste the value from another table into each record.

This would probably be possible by adding a field with a null value to true, and then adding values, and then changing the value to nullable to false. However, I am interested to know if this can be done in one shot.

+6
source share
2 answers

You have to make sure that another table has a my_field value for each entry in my_table.

 class AddMyFieldToMyTable < ActiveRecord::Migration def up add_column :my_table, :my_field, :text execute("insert into my_table(my_field) values (select my_field from different_table where my_table.id = different_table.different_id)") change_column :my_table, :my_field, :text, null: false end def down remove_column :my_table, :my_field end end
+6
source

All indications show that this cannot be done in one shot; you will need to add a column without a null constraint, fill in the data, and then add a null constraint.

 class AddMyFieldToMyTable < ActiveRecord::Migration def change add_column :my_table, :my_field, :text reversible do |dir| dir.up do # populate my_field col change_column :my_table, :my_field, :text, null: false end end end end

Resources

If you really want to set a column once, perhaps you can generate it with a temporary default value and then update it immediately with real data.

 class AddMyFieldToMyTable < ActiveRecord::Migration def change add_column :my_table, :my_field, :text, default: 'tmp', null: false reversible do |dir| dir.up do # populate my_field col end end end end
+1
source

More articles:


All Articles