Does Ecto.Migration.modify/3 allow removal of default values?

Questions / Help
, ,
1

Hi all! First time posting, let me know if I can structure my question better.

I am using Ecto migrations on a project and need to add columns with non-NULL constraints to a table with existing data. My down simply removes the columns. My up adds the columns, uses default values to get placeholder data in the DB, flushes, and then uses Ecto.Migration.modify/3 to add the non-NULL constraints and remove the defaults. However, when I look at the table description in Postgres, I see not null default NULL::character varying. It looks like the database still behaves properly, but shouldn’t this modifier be gone entirely?

Here’s my migration’s up:

  def up do
    # Step 1. Add location fields with default values
    alter table(:campuses) do
      add :street_1, :string, default: "__INVALID_STREET"
      add :street_2, :string
      add :city, :string, default: "__INVALID_CITY"
      add :state, :string, size: 2, default: "ZZ"
      add :zip, :string, size: 16, default: "__INVALID_CITY"
      add :lon, :float, default: 0.0
      add :lat, :float, default: 0.0
    end

    # Step 2. Execute above commands.
    flush()

    # Step 3. Remove default option and add null constraint
    alter table(:campuses) do
      modify :street_1, :string, null: false, default: nil
      modify :street_2, :string
      modify :city, :string, null: false, default: nil
      modify :state, :string, size: 2, null: false, default: nil
      modify :zip, :string, size: 16, null: false, default: nil
      modify :lon, :float, null: false, default: nil
      modify :lat, :float, null: false, default: nil
    end
  end

Thanks all!

2 Likes
2

Have you tried simply removing the default: nil options and re-run the migration?

3

@pinksynth It’s been a while, but did you find a solution for this?

4

From Postgres documentation

To remove any default value, use:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

This is effectively the same as setting the default to null. As a consequence, it is not an error to drop a default where one hadn’t been defined, because the default is implicitly the null value.

2 Likes
5

@florish Thanks for asking! It has been a long time, I do not recall what the solution was. In retrospect I embarrassingly don’t really understand the problem either :sweat_smile:. Perhaps there is some difference between “No default” and “Default: null”, but I don’t think that difference had any practical impact on the project or code. Thanks @joddm for the docs reference.

1 Like
6

Yes, well, that’s a good question indeed. Out of curiosity, I’ve checked the PostgreSQL documentation, and it’s mostly a difference between an implicit NULL (which is the default if no DEFAULT is set in PostgreSQL) and an explicit NULL default value:

DEFAULT default_expr

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. (…)

If there is no default for a column, then the default is null.

So practically no impact, the only difference is the presence / absence of an expliciet DEFAULT NULL statement in your CREATE TABLE SQL code.

1 Like