Opt-in trim_scale for decimal fields

[IlyaSemenov]

The default Postgres behavior for decimal fields math operations is that the result has the precision of the maximum precision of its elements. That means, 0.333 + 0.367 is not 0.7 but 0.700:

await db.user.insert({ balance: 0.333 })
await db.user.find(1).increment({ balance: 0.367 })
console.log(await db.user.find(1).get("balance")) // Expected 0.7, actual result 0.700

This is inconvenient for real use. I end up using raw SQL for all math operations:

await db.user.insert({ balance: 0.333 })
await db.user.find(1).update({ balance: sql`trim_scale(balance + (${0.367}))` })
console.log(await db.user.find(1).get("balance")) // 0.7

The same problem is with aggregates:

await db.user.insertMany([{ balance: 0.333 }, { balance: 0.367 }])
console.log(await db.user.sum("balance")) // Expected 0.7, actual result 0.700

Question, would it be somehow possible to automatically apply trim_scale on the ORM level in increment/decrement and aggregate operations? Perhaps t.decimal() options could be changed to be an object with optional keys, including the new flag:

{
  balance: t.decimal({ precision, scale, trimScale: true })
}

[romeerez]

I don't know why Postgres does that by default, but why is it a problem?

That's a lot of work to support such SQL manipulations on a column level.

[romeerez]

So if that's a real problem, I'll keep this in plans, and allowing to specify that column the x needs to actually turn into some SQL expression when selecting may be useful here and in other use cases. But that's a lot of effort, and if it's not a big deal, there is no need to implement that.

[IlyaSemenov]

That's not exactly big deal, it's a "nice to have" one. So if you feel this does not belong to the backlog of how the ORM could be extended in future then sure can close the issue.