Thread: Implement a new data type

On Tue, 2020-08-11 at 11:31 +0000, mohand oubelkacem makhoukhene wrote:
> I whould like to implement a new data type next to char, number, varchar... A Money type. 
> So i'll have to change the source code, but i don't know which fonctions i need to change and which part to work on.

You don't need to change the source, you could create an extension
using a shared library that can be loaded into the server at run time.

The documentation has more:
https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-C-BASETYPE
https://www.postgresql.org/docs/current/extend-extensions.html
https://www.postgresql.org/docs/current/extend-pgxs.html

Yours,
Laurenz Albe

On 8/11/20 4:31 AM, mohand oubelkacem makhoukhene wrote:
> Hello;
> I whould like to implement a new data type next to char, number, 
> varchar... A Money type.

One that is different from the current one?:

https://www.postgresql.org/docs/12/datatype-money.html

> So i'll have to change the source code, but i don't know which fonctions 
> i need to change and which part to work on.
> I need some help to implement this new data type.
> Thank you and best regards
> Mohand
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Aug 11, 2020 at 06:38:39AM -0700, Miles Elam <miles.elam@productops.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. :-)

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.

cheers,
raf

> On Aug 11, 2020, at 8:01 PM, raf <raf@raf.org> wrote:
>
> On Tue, Aug 11, 2020 at 06:38:39AM -0700, Miles Elam <miles.elam@productops.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
usePython which, like most (all?) languages that rely on the underlying C library for floating point support, is
vulnerableto floating point noise. Python has a fixed precision type, and like Postgres it also accepts character and
floatinput. 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
willget 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

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

> > On Aug 11, 2020, at 8:01 PM, raf <raf@raf.org> wrote:
> > 
> > On Tue, Aug 11, 2020 at 06:38:39AM -0700, Miles Elam <miles.elam@productops.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