Ecto Migration -- Setting a default for existing rows for a new not null column

Phoenix Forum Questions / Help
,
1

When I add a column to an existing table, I’d like to set a default value for the rows already in the table, but I don’t want there to be a default value for any newly added rows. I also want it to be a not-null column.

The problem is that if I don’t set a default, the not null constraint gets violated and the migration fails. What I’m looking for is a simple way to specify a default for existing rows, while still causing any future inserts to fail if a value is not specified for that column.

This feels like a relatively common situation, and it would be nice not to have to break things out and write up() and down() functions and instead just use a change() and specify a “default for existing rows”.

An explicit mechanism to either specify a value or call a function to fill in data prior to proceeding with setting the column not null would seem like a natural way to solve this. I haven’t found any indication this exists, but the situation I am running into seems like it would be a fairly common problem.

Here’s an example of an approach I sometimes use. It feels like way too much code for what I’m wanting to achieve.

defmodule Rtphx.Repo.Migrations.ExampleAddColumn do
  use Ecto.Migration

  def up do
    alter table(:example) do
      add :newcolumn, :string, default: "defaultvalue", null: false
    end
    alter table(:example) do
      modify :newcolumn, :string, default: nil, null: false
    end
  end

  def down do
    alter table(:example) do
      remove :newcolumn,  :string, null: false
    end
  end

end
3 Likes
2

An Ecto migration using the builtin macros is pretty much a 1:1 mapping to the databases underlying DML so I think your code is exactly as much as it needs to be.

3
  1. Have a default value in your migration.
  2. Make your Ecto schema module validate the new field/column as mandatory so Ecto will never issue an SQL INSERT / UPDATE without it, thus never using the default value specified in the database.
4

I guess I’m spoiled by being able to just write a “change” function most of the time, where Ecto figures out the reverse migration for me.

5

You could have used this :

  def change do
    alter table(:example) do
      add :newcolumn, :string, null: false
    end
    execute "update example set newcolumn = whatever_you_want"
  end```
3 Likes