I use Phoenix and Ecto in one of my project.
I want to add a column to one table, and I expect it to be a NOT NULL column. But I already have some existed data, so I decide to add the column, update all rows to some value and modify the column to NOT NULL.
I tried these two code:
# solution 1
def up do
alter table(:channels) do
add :type, :integer
Exchat.Repo.update_all("channels", set: [type: 1])
modify :type, :integer, null: false
end
end
# solution 2
def up do
alter table(:channels) do
add :type, :integer
end
Exchat.Repo.update_all("channels", set: [type: 1])
alter table(:channels) do
modify :type, :integer, null: false
end
end
Both of them didn't work and I got error like:
23::40::07.514 [info] == Running Exchat.Repo.Migrations.AddTypeToChannels.up/0 forward
23::40::07.541 [debug] UPDATE "channels" AS c0 SET "type" = $1 [1] ERROR query=12.0ms
** (Postgrex.Error) ERROR (undefined_column): column "type" of relation "channels" does not exist
(ecto) lib/ecto/adapters/sql.ex:383: Ecto.Adapters.SQL.execute_and_cache/7
I'm not sure if it's related to the Ecto version but my Ecto version is 2.0.0-rc.0.
Is there any way to achieve this? Or is there something wrong in my example code?
Ecto migrations are used to do the following: Change the structure of the database, such as adding fields, tables, or indexes to improve lookups. Populating fields with modified data or new data.
To roll back to a version number, supply --to version_number . To roll back a specific number of times, use --step n . To undo all applied migrations, provide --all .
setup , Ecto tries to create the repository database, load the database structure present in the structure. sql file, run any pending migrations and finally run the seeds. However, unfortunately, it doesn't work in Ecto SQL versions before 3.1.
Solution #2 is the correct approach, but you need to add a call to Ecto.Migration.flush/0
after the first alter
block to make sure all the changes are executed on the database instantly instead of being queued up to be executed later, which is the default.
def up do
alter table(:channels) do
add :type, :integer
end
flush()
Exchat.Repo.update_all("channels", set: [type: 1])
alter table(:channels) do
modify :type, :integer, null: false
end
end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With