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

Hi

út 11. 8. 2020 v 13:31 odesílatel mohand oubelkacem makhoukhene <
mohand-oubelkacem@outlook.com> napsal:

Hello;
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.
I need some help to implement this new data type.

If you need to implement just a new data type, then you don't need to
modify Postgres source code. You can write your own extension.

For own custom type you need to write minimally "in" and "out" function,
and then you can run "CREATE TYPE" statement

one example of custom type can be a extension
https://github.com/okbob/pgDecimal

Regards

Pavel

Thank you and best regards

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.

On Tue, Aug 11, 2020 at 5:02 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

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

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

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

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