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?
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Develop your skills with books, videos, and courses.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #javascript
- #code-sync
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
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.
Real-time error tracking, performance insights, and observability for devs.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Courses that'll move you from confusion to "Aha, now I get it!"