On Wed, Aug 12, 2020 at 12:44:21PM -0400, Philip Semanchuk <philip@xxxxxxxxxxxxxxxxxxxxx> wrote: > > On Aug 11, 2020, at 8:01 PM, raf <raf@xxxxxxx> wrote: > > > > On Tue, Aug 11, 2020 at 06:38:39AM -0700, Miles Elam <miles.elam@xxxxxxxxxxxxxx> 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
