Re: Implement a new data type

On Wed, Aug 12, 2020 at 2:01 AM 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.
>

It is worth noting that decimal is an alias for numeric in Postgres. For
that reason you will have less confusion if you use numeric instead.

>
> 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. :-)
>

I don't think the type designation buys you anything. unless it is a part
of an arithmetic expression The single quotes do and cannot be omitted
here.

So I think there is a difference between ('1.23' + 1)::numeric and
'1.23'::numeric + 1 but there is also a difference between 1.23::numeric +
1 and '1.23'::numeric + 1

But there is no reason to add the cast when doing something like an insert
of a single value.

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

The existing money type has another problem in that the currency it is
attached to is taken from the current locale. So if you change your locale
settings you can change a value from, say, 100 IDR to 100 EUR at least for
display purposes.

I have some thoughts about how to do a multi-currency type but I am not
actually sure you get anything by tying the data together instead of having
it in separate columns.

>
> cheers,
> raf
>
>
>
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more