@thiagomajesk, its a good conversation and always appropriate to talk about storage of and calculations on money. Here are some of my thoughts on microdollars, floating point and serialising money in general.

Floating point is a bad idea for representing money

As you noted, there is a general recommendation not to use floats/doubles. Floating point is, by definition, making tradeoffs between size, performance and precision and accuracy that are different to the requirements for money. At the simplest level, floating point representations have issues with precision and associativity. For example:

# Accuracy cannot be guaranteed
iex> 0.1 + 0.2
0.30000000000000004

# Not associative
iex> a = 1.0000001
iex> b = 2.0000002
iex> c = 3.0000003
iex> (a + b) + c
6.0000006
iex> a + (b + c)
6.000000600000001

Integers aren’t perfect either

Integers are a better fit, they can at least represent a set of real numbers up to a certain precision (limited by the machine word size typically). It works appropriately (for money) for addition, subtraction and multiplication - but we still have issues with division. A good example is what happens when we divide 10 by 3:

# The infinite series of 3.3333...... is rounded
iex> 10 / 3
3.3333333333333335
# Integer division won't round trip
iex> div(10, 3) * 3
9

The issue of integer division isn’t solved by microdollars. This is one of the reasons why the Money.split/2 exits. It does division but also returns any remainder so that (money / number) * number + remainder == money is a guarantee.

Decimals

Decimal implementations, in computers, make different tradeoffs to precision, scale and speed. Whereas floating point emphasises speed, decimal tends to prioritise precision and scale. In many implementations, including the Decimal, the precision can be arbitrarily large but defaults to 28 digits.

iex> Decimal.Context.get()
%Decimal.Context{
  precision: 28,
  rounding: :half_up,
  flags: [],
  traps: [:invalid_operation, :division_by_zero]
}

Note too the rounding: :half_up. There are several rounding strategies in Decimal and its worth being familiar with them if you’re working with money. ex_money uses :half_even as its default rounding since that is most common in financial applications and is even known as banker’s rounding.

So why can Decimal preserve prevision better than floating point? In part because many decimal libraries, including Decimal represent the decimal number as an integer with an associated scale factor (to indicate where the decimal place is located). Decimal also stores an exponent which can optimise the size of the integer part without losing precision. Postgres which has a very similar type called numeric stores only the integer and scale.

Serialisation

Not all interchange formats or storage formats support decimal data. Thankfully Postgres and its many descendant databases do with the numeric data type. In addition ex_money_sql provides Ecto and Postgres data types that combines the currency code with the money amount into a single data type to maximise data integrity - the design goal is to never be in a position where the currency type is unknown or ambiguous.

For interchange formats that have no decimal data type, like JSON, the amount is cast to a string since thats the only JSON data type that can preserve precision and scale. In fact money is cast to a JSON object of the form {\"currency\":\"USD\",\"amount\":\"100\"}.

On Precision and Scale

The microdollar format has the advantage of performance while maintaining a fixed scale or 6 digits. However financial calculations are expected to retain 7 or 8 digits of scale during calculations - think stock market, home loan interest. And as @LostKobrakai points out, Digital Tokens (crypto) may require more than 6 digits of scale. Bitcoin calculations may need up to 10 digits of scale.

Finally

Money and Float make different tradeoffs amongst performance, precision, scale and accuracy. Float emphasises performance. ex_money prioritises precision, scale and accuracy. microdollar aims for a middle ground. Each approach has its benefits and compromises.

When it comes to money, I believe precision, scale and accuracy are paramount and thats why ex_money uses Decimal for Elixir representation, a composite data type called money_with_currency for Postgres and a string-based object format for JSON (including embedded Ecto schemas.

ex_money is built upon ex_cldr so localisation is an intrinsic part of the library:

iex> m = Money.new(:USD, 100)
Money.new(:USD, "100")

# Locale-specific formatting
iex> Money.to_string(m, locale: :de)
{:ok, "100,00 $"}

iex> Money.to_string(m, locale: :de, format: :long)
{:ok, "100 US-Dollar"}

iex> Money.to_string(m, locale: :es, format: :long)
{:ok, "100 dólares estadounidenses"}

# Locale specific parsing (which take care to understand
# the locales decimal and grouping separators
iex> Money.parse("100,00 $", locale: :de)
Money.new(:USD, "100.00")

# Return display names that can be used in UI for
# example, as column headings
iex> Cldr.Currency.display_name :USD, locale: :de
{:ok, "US-Dollar"}

iex> Cldr.Currency.display_name :USD, locale: :ar
{:ok, "دولار أمريكي"}

And although not strictly localisation, ex_money knows all the ISO4217 currency formats so rounding is done to the correct number of digits when required and formatting displays the correct number of decimals. For example:

# JPY has no decimal places (in common use at least)
iex> Money.to_string Money.new(:JPY, 54321)
{:ok, "¥54,321"}

# Tunisian Dinar has three decimal places
iex> Money.to_string Money.new(:TND, "1000.12345")
{:ok, "TND 1,000.123"}

# Swiss Franc cash smallest unit is 5 centimes.
# Rounding is `:half_even`
iex> Money.round Money.new(:CHF, "123.73"), currency_digits: :cash
Money.new(:CHF, "123.75")

I’ve sent a proposal to the Ecto mailing list about deriving the database type from an Ecto.ParameterizedType (like Money.Ecto.Composite.Type) and casting to it since Ecto already knows the type involved.

Unless that proposal is accepted (and I consider it a low change of success) it will be necessary to either use:

# as you are already doing
where: b.current_balance > type(^Money.zero(:USD), b.current_balance)

# or
where: b.current_balance > fragment("?::money_with_currency", ^Money.zero(:USD))

Note that Money.zero/1 is an explicit function for returning a 0 amount money in some currency.

I will go ahead and write some custom operators for :money_with_currency since there is still the risk of comparing money of different currencies which is not semantically correct.

I’m very late to this thread, apologies. I’m glad you got a resolution - and I agree that having to use the type/2 macro isn’t very ergonomic. I’ll see if following the error messages advice and building a binary extension can make this better.

And I should also define custom comparison operators because otherwise Postgres will happily compare two money_with_currency types, even if they are of a different currency. This because Postgres, like the BEAM, has built-in term ordering.