Re: Implement a new data type

> On Aug 11, 2020, at 8:01 PM, raf <raf(at)raf(dot)org> wrote:
>
> 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. :-)

Yes, I agree, this is also important (and easy to overlook) if you’re accessing the database via a non-SQL language. We use Python which, like most (all?) languages that rely on the underlying C library for floating point support, is vulnerable to floating point noise. Python has a fixed precision type, and like Postgres it also accepts character and float input. The float input can give surprising results.

>>> decimal.Decimal('1.79') # This is OK
Decimal('1.79')
>>> decimal.Decimal(1.79) # This will not end well!
Decimal('1.79000000000000003552713678800500929355621337890625')
>>>

In the case of a Postgres column like numeric(10,2), input like 1.79000000000000003552713678800500929355621337890625 will get rounded to 1.79 anyway and no harm will be done. But like you said, raf, it’s a good idea to be too paranoid. :-)

Cheers
Philip