> 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
