Re: Implement a new data type

On Tue, Aug 11, 2020 at 06:38:39AM -0700, Miles Elam <miles(dot)elam(at)productops(dot)com> wrote:

> Also of note: PostgreSQL already has a money type (
> https://www.postgresql.org/docs/current/datatype-money.html)
> But you shouldn't use it (
> https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money).
>
> I only bring it up so that you can know to make your money type a slightly
> different name to avoid a conflict. Money is deceptively hard to implement
> correctly. I'd recommend reading the second link if you have not already to
> avoid previously known issues.

I use decimal(10,2) for whole cents, and decimal(12,6)
for sub-cents. Single currency only. I didn't know
there was a money type originally, but it wouldn't be
usable for me anyway without the ability to specify the
scale and precision.

I recommend considering passing values to the database
as "decimal '1.23'" rather than bare numeric literals,
just so there's no chance of the value being
interpreted as a float at any stage by postgres. Maybe
that's being too paranoid but that's a good idea when
it comes to money. :-)

Perhaps the incorporation of currency would make a new
money type interesting. Currencies probably don't
change as often as timezones but there would probably
still be some ongoing need for updates.

cheers,
raf