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

Questions & Help Questions
,
4

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