Ecto is returning Decimal from `sum/1` after changing column type from `int` to `bigint` in Postgres

Questions / Help
,
1

We are migrating a column from int to bigint and a bunch of our unit tests started failing because of errors like this: (ArithmeticError) bad argument in arithmetic expression: #Decimal<10> + 10. It turns out that everywhere we are doing a sum/1 over this column it is now returning a Decimal instead of an integer like it used to. I’m not sure if this is a bug or not. If this is expected behavior, is there a way to get it to return a number besides just putting type(sum(row.column), :integer) everywhere?

2 Likes
2

That’s what I would expect, I believe Ecto maps both :bigint and :numeric Postges types to Decimal. Maybe you could try accessing your data using an Ecto schema and define the field as :integer.

3

I am already doing that:

field(:amount, :integer, source: :amount_big)

And it works fine for querying just the field. It isn’t until I do something like this, that I get a Decimal:

from(t in Table,
  group_by: t.company_id,
  select: sum(t.amount),
  where: t.company_id == ^company_id
) |> Repo.one()
4

Oh, that explains. You’re summing in the DB and Ecto has therefore no idea that you would like the result to be an :integer.

In this case, no, I can’t think of anything other than the solution you already came up woth.

3 Likes
5

@tmock12 helped me figured out the issue. Even though Elixir/Erlang integers can be of arbitrary size, Postgres ints aren’t the same. When you do aggregates in Postgres it will potentially change the type, as can be seen here:

So pg_typeof(sum(1::int)) is bigint which are both just integers in Elixir, but pg_typeof(sum(1::bigint)) is numeric, which is a Decimal in Elixir. So Ecto is doing everything correctly and I think you’re right, the only solution is to call type(sum(t.amount), :integer) everywhere.

6 Likes