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!