CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
Francis.Beauchamp
Francis.Beauchamp

Reputation: 1373

Add NOT NULL field without default value into a populated DB

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.

Upvotes: 10

Views: 3587

Answers (3)

mefryar
mefryar

Reputation: 363

In my case I need to deviate from @usha's answer in two ways: 1) I needed to UPDATE rather than INSERT INTO since I'm updating the NULL values of existing rows rather than creating new rows. 2) I needed to use change_column_null instead of change_column ... null: false. A stylized example is below.

class AddMyFieldToMyTable < ActiveRecord::Migration
  def up
    add_column :my_table, :my_field, :text
    execute <<-SQL
      WITH values AS (
        SELECT
          my_table.id AS my_table_id,
          different_table.my_value
        FROM my_table
        JOIN different_table ON
          my_table.id = different_table.different_id
      )
      UPDATE my_table
      SET my_field = values.my_value
      FROM values
      WHERE my_table.id = values.my_table_id
    SQL
    change_column_null :my_table, :my_field, false
  end

  def down
    remove_column :my_table, :my_field
  end
end

Upvotes: 0

Carlos Ramirez III
Carlos Ramirez III

Reputation: 7434

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:

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

Upvotes: 1

usha
usha

Reputation: 29349

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

Upvotes: 9

Related Questions