Ecto Migration: `not_null_violation` when modifying column null->non-null & adding default value

spencer.christensen

Howdy y’all,

I’m brand new to Elixir and have what is likely a simple problem. I created an Ecto schema with a votes column that is type :integer and nullable. I later realized it should be non-nullable and default to 0. I made a migration to perform this update, however I am getting the error:

** (Postgrex.Error) ERROR 23502 (not_null_violation) column "votes" of relation "faqs" contains null values

Migration

defmodule Pento.Repo.Migrations.AlterFaqsAddDefaultVoteValue do
  use Ecto.Migration

  def change do
    alter table(:faqs) do
      modify :votes, :integer, default: 0, null: false
    end
  end
end

See below for my existing table and full error output. I found a comment from Jose Valim stating “The default value in migration is used to set the value for existing rows.” in 2015. I read that as stating that the default value in a migration should change any existing null fields to the stipulated value. Is that not the case and should I just use a separate execute for altering existing rows?

Existing Table

id	question	answer	votes	inserted_at	updated_at
1	Question 1	Answer 1	NULL	2024-02-04 02:54:39	2024-02-04 12:51:01
2	Question 2	Answer 2	NULL	2024-02-04 02:56:24	2024-02-04 02:56:24

CREATE TABLE "public"."faqs" (
    "id" int8 NOT NULL DEFAULT nextval('faqs_id_seq'::regclass),
    "question" varchar(255),
    "answer" varchar(255),
    "votes" int4,
    "inserted_at" timestamp(0) NOT NULL,
    "updated_at" timestamp(0) NOT NULL,
    PRIMARY KEY ("id")
);

Output

07:05:36.729 [info] == Running 20240204125254 Pento.Repo.Migrations.AlterFaqsAddDefaultVoteValue.change/0 forward

07:05:36.730 [info] alter table faqs
** (Postgrex.Error) ERROR 23502 (not_null_violation) column "votes" of relation "faqs" contains null values

    table: faqs
    column: votes
    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.16.0) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1161: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.11.1) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.16.0) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.11.1) lib/ecto/migration/runner.ex:311: Ecto.Migration.Runner.perform_operation/3
    (stdlib 5.2) timer.erl:270: :timer.tc/2
    (ecto_sql 3.11.1) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8

Thank you for any help!

5 comments

#ecto #ecto-migration

0 1820 5

2024-02-05 15:48:15 UTC

Showing Posts 1 to 5

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

dimitarvp

Either make one migration that only introduces the default (without the non-null constraint) or just make a data migration that fills up all null column values with the default value.

I am not sure the former would work at all so I’d go for the latter i.e. something like this:

defmodule YourApp.Repo.Migrations.SetDefaultValueToFaqs do
  use Ecto.migration

  def up do
    execute("UPDATE faqs SET votes = 0 WHERE votes IS NULL")
  end

  def down do
    # Do the reverse here if it makes sense.
  end
end

After this one above, make the one that introduces the non-null constraint.

Post #1

al2o3cr

You may need to split this into two migrations, since the alter implementation will always change the NOT NULL constraint before the DEFAULT:

github.com/elixir-ecto/ecto_sql

lib/ecto/adapters/postgres/connection.ex

629b6633b


      
          modify_null(name, opts),
          modify_default(name, type, opts)

I’d guess this was more relevant in older PGs when setting a default on a nullable column was an expensive “rewrite every row that has a NULL” operation.

Post #2

spencer.christensen

Thank you for pointing me to that file! I’m no expert in Postgres, but it looks like this is in fact a limitation in PG as running both:

ALTER TABLE faqs ALTER COLUMN votes SET NOT NULL, ALTER COLUMN votes SET DEFAULT 0 (the unquoted ordering from connection.ex)

and

ALTER TABLE faqs ALTER COLUMN votes SET DEFAULT 0, ALTER COLUMN votes SET NOT NULL

results in the same error:

ERROR: column "votes" of relation "faqs" contains null values

Changing the order of the statements has no effect.

Thus it looks like this is indeed a multi-step migration: fill existing rows and then set the default and not null:

defmodule Pento.Repo.Migrations.AlterFaqsAddDefaultVoteValue do
  use Ecto.Migration

  def change do
    execute "UPDATE faqs SET votes = 0"

    alter table(:faqs) do
      modify :votes, :integer, default: 0, null: false
    end
  end
end

Post #3

dimitarvp

Will you not lose information this way? What about rows that have, say, faqs = 10?

Post #4

spencer.christensen

Indeed I would!! I didn’t include that as I’m just playing with a demo w/ no existing vote counts, but that doesn’t mean I shouldn’t out of best practice. I appreciate your attentive eye.