Add NOT NULL field without default value into a populated DB

Question

I have a table, let's call it MyTable. It is part of a Postgresql database.

In MyTable are a lot of entries, let's say over a million. I would like to add a field to this table, let's call it MyNewField. It is to be added by an ActiveRecord Migration.

This field is to be without default values and not nullable. The result, in it's migration class would be something like so:

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

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

What I would like to do is: Add the row without default value and nullable set to false (without triggering a PG::NotNullViolation). Then, insert a value from another table into each records.

This would probably be achievable by adding the field with nullable set to true, then adding the values, then changing back to nullable set to false. However, I am interested to know if it is possible to do so in a single shot.

Answer 1

You have to make sure that the other table has the 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

Answer 2

All indications seems to show that this will not be possible to do in one shot; you'll have to add the column without the null constraint, populate the data, then add the null constraint afterwards.

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:

• How to solve "Cannot add a NOT NULL column with default value NULL" in SQLite3?
• http://strd6.com/2009/04/adding-a-non-null-column-with-no-default-value-in-a-rails-migration/

---

If you really only want to set the column once, perhaps you could generate it with a temporary default value then immediately update it with the 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