@kip You might know if this is possible or not.
I have been experimenting with different ways of updating a
balance field in the database that is of the usual
fn _ ->
from(w in Wallet,
where: w.id == ^wallet_id,
update: [inc: [balance: type(^amount, ^Money.Ecto.Composite.Type.cast_type())]]
This results in an error:
** (Postgrex.Error) ERROR 42883 (undefined_function) operator does not exist: money_with_currency + money_with_currency
query: UPDATE "wallets" AS w0 SET "balance" = w0."balance" + $1::money_with_currency WHERE (w0."id" = $2)
hint: No operator matches the given name and argument types. You might need to add explicit type casts.
I have run the “aggregate functions for money” migration although I didn’t think it would help since this is
I think maybe this kind of syntax just isn’t possible? Can anyone confirm?
September 25, 2021, 12:00am
I’ve just published
ex_money_sql version 1.5.0 that adds support for the Postgres for
:money_with_currency types. I believe that will also
support the query you described above.
To install the required functions in Postgres
the readme now says:
ex_money defines a migration generator which, when migrated to the database with
mix ecto.migrate, supports the
+ operator for
:money_with_currency columns. The steps are:
Generate the migration by executing
Migrate the database by executing
Formulate an Ecto query to use the
iex> q = Ecto.Query.select Item, [l], type(fragment("price + price"), l.price)
#Ecto.Query<from l0 in Item, select: type(fragment("price + price"), l0.price)>
iex> Repo.one q
[debug] QUERY OK source="items" db=5.6ms queue=0.5ms
SELECT price + price::money_with_currency FROM "items" AS l0 
Let me know if you have any feedback and please file any bugs on the
What amazing timing!
Thanks @kip . You’re genuinely an idol.
I’ll test that now and write the result here.
Update: I tried it for the use case described above and it works perfectly!
September 25, 2021, 1:19pm
Good to hear! Was a fun Saturday morning puzzle for me. And always good to be reminded how amazing Postgres is!
@slouchpie @kip How were you able to integrate money into the update all inc statement? Have been trying for a while and can’t figure it out
October 26, 2022, 11:00pm
ex_money_sql is the library that implements database support for
Money.t types. After that, its Postgres (or MySQL potentially or some other database) that is doing all the work.
+ operator can be overloaded in Postgres and the implementation of that for a
:money_with_currency type is
Did you try the code in my OP?
I don’t have source code for it any more - we transitioned to eventsourcing (
commanded lib) for our wallets stuff.