unsupported type MONEY in macros with Postgres

[IdemenB]

Running a query on a Postgres DB table with a field of MONEY type gives the following error with the query_as! macro. This happens despite the fact that the related field in the Struct field is set as "PgMoney" documented in https://docs.rs/sqlx/0.5.2/sqlx/postgres/types/struct.PgMoney.html.

unsupported type MONEY of column #5 ("paymentamount")

Am I missing something here?

[EvanCarroll]

Using the money type in PostgreSQL is a horrible idea.

[abonander]

@IdemenB for the macros to support MONEY, PgMoney (also Vec<PgMoney> and &[PgMoney]) would have to be added to this list: https://github.com/launchbadge/sqlx/blob/master/sqlx-macros/src/database/postgres.rs

Using the money type in PostgreSQL is a horrible idea.

That's... complicated. It was deprecated in old versions but it no longer is. Here's the author of the type weighing in (also talking about how the parsing from the text format is pretty sloppy): https://www.postgresql.org/message-id/flat/20130328092819.237c0106@imp#20130328092819.237c0106@imp

Even as the author I sometimes go with numeric but there is a place for
the type. If you are working with simple dollars and cents quantities
and you need to do lots of calculations on them, the money type can be
a great performance boost. The big win that money brings is that
everything is stored as an int. That means that you don't need to
convert data in the database to a machine representation before
summing, averaging, etc. The machine can generally work on the data as
it comes out of the DB.

The reply also explains some cases where you don't want to use it:

• You need fractional amounts of the currency (such as US gas prices that do, e.g. $3.899 / gal)
• You need to support more than one currency in the column database

Certainly if you do choose it, you need to be keenly aware of its limitations, but to say it's a "horrible idea" is hyperbole.

[rustalot]

+1 for moneytype!
Went ahead and deleted the bounty, in case that's not appropriate here.
I'd still like to see support for money, as I work with some databases that have not migrated away from money type (and likely won't). But I understand it is relatively depreciated.

[EvanCarroll]

First, I still contend this is deprecated and provides no real advantage. There is no reason to ever use this over numeric. But that aside, if the upstream devs acknowledge that that the "parsing from the text format is pretty sloppy" what's the plan to recreate the sloppyness here to know if something can be converted to money?

[mehcode]

The PgMoney type in SQLx does not support text parsing (because it's vague and highly locale dependent). However, the type support does exist.

If you want to use the money type in macros today, all that would need to be done is SELECT money as "money: PgMoney", .... This is the type override syntax to tell the macros to use a specific type for a column that the macros wouldn't otherwise choose. For native support, that linked PR would need to be merged.

I also am firmly against the idea of the money type. It's about as bad as using the timestamp type (over timestamptz). Either store it as a numeric or store it as an integer of the lowest denomination. However, SQLx tries to not have an opinion for things like this.

[abonander]

Closed by #1218