Re: Implement a new data type

On Wed, Aug 12, 2020 at 12:44:21PM -0400, Philip Semanchuk <philip(at)americanefficient(dot)com> wrote:

> > 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

Chris Travers pointed out to me that Postgres itself
parses floating point literals as the numeric type
(i.e. "select pg_typeof(1.23);" returns numeric) so
Postgres has made the right choice for its parser,
unlike most(?) languages (except raku). But yes, in
Python, it's decimal.Decimal with integer/string input
all the way (with dec=decimal.Decimal if you have a lot
of them).

cheers,
raf