spencer.christensen
Ecto Migration: `not_null_violation` when modifying column null->non-null & adding default value
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!
Most Liked
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.
al2o3cr
You may need to split this into two migrations, since the alter implementation will always change the NOT NULL constraint before the DEFAULT:
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.
dimitarvp
Will you not lose information this way? What about rows that have, say, faqs = 10?
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
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
We build reliable cloud platforms for business-critical systems.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"