How to update a column to NULL

Hello everyone.
Fairly new to Elixir/Phoenix :wink:
I am trying to set the value of a column to NULL using Ecto.
I have set in my ecto schema field the field :confirmation_token, :string, allow_nil: true and
in my migration add :confirmation_token, :string.
I am trying to achieve this with
user_changeset = Ecto.Changeset.change(user, %{confirmation_token: nil, confirmed: true, confirmed_at: DateTime.utc_now()})
Repo.update!(user_changeset)
The resulting query is
[debug] QUERY OK db=42.4ms UPDATE "users" SET "confirmed" = $1, "confirmed_at" = $2, "updated_at" = $3 WHERE "id" = $4 [true, {{2017, 2, 8}, {19, 3, 50, 522568}}, {{2017, 2, 8}, {19, 3, 50, 556733}}, 1].
So as you can see the confirmation token is not getting update to NULL.
How can I achieve this behaviour (updating a column to NULL)?

Than you very much in advance!

1 Like

Stupid question: are you sure the confirmation_token is not null in the user struct you are passing at this moment:

user_changeset = Ecto.Changeset.change(user, %{confirmation_token: nil, confirmed: true, confirmed_at: DateTime.utc_now()})

I think it should only generate appropriate SET clause for the fields that did actually change. So if you have nil in the struct already, it won’t be visible in update query at all.

2 Likes

This is strange…
This is where my user struct comes from.

query = from User, where: [confirmation_token: ^token], select: [:id] case Repo.all(query) do [user | _tail] -> user_changeset = Ecto.Changeset.change(user, %{confirmation_token: nil, confirmed: true, confirmed_at: DateTime.utc_now()}) Repo.update!(user_changeset)

If I delete select: [:id] from the query everything seems to work.
So I guess that I should rewrite my select like select: [:confirmation_token, :confirmed_at, :confirmed]
But this raises another question.
In my code I am missing all three of the attributes that I am updating (confirmed_at, confirmed, confirmation_token ).
Why are confirmed and confirmed_at updated correctly?

Off-topic: Just in case someone brings this up, I notice that my query should be Repo.one instead of Repo.all and I should also have a unique constraint on confirmation_token in my database. :smiley:

1 Like

This actually makes a lot of sense. In your original struct the field value was resolved to nil when accessed. So changing it from nil to nil reaulted in nothing to be updated

2 Likes

Ok now I get it…
Thank you very very much!! :heart_eyes:

1 Like