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

pinksynth

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!

6 comments

#ecto #ecto_sql #ecto-migration

2 4462 6

2024-08-28 14:01:22 UTC

Showing Posts 1 to 6

Show Best Posts
Show All (oldest first)
Show All (newest first)

dimitarvp

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

Post #1

florish

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

Post #2

joddm

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.

Post #3

pinksynth

@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 . 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.

Post #4

florish

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.

Post #5

dimitarvp

I can confirm that this worked for me in a migration. I needed to change the type of a column so I did the dance with making a new column, migrating the data from the old one to the new one but the new one had to allow a default value because the migration was multi-step and then when everything was done I made a migration to delete the old column and to remove the default value from the new column while still disallowing NULL / nil.

Post #6

— All posts loaded —