Unable To Process Numeric Type

[R-K-H]

Hello,

Forgive my ignorance for the types configuration parameter, but is there a way to process numeric's when setting up a query? My work around so far has been type casting through the use of ::NUMERIC, but I have to hard code the value into the query for the library to play nice with everything else.

Is there an override I might apply or something I can do (I've even cast it as DataType.Numeric, but that doesn't help as it's not supported).

Thanks,

[malthe]

There currently is no support for the numeric type – mostly because there is no obvious type on the JavaScript-side.

But you can cast it to text on the database-side and then parse the representation into your desired value type. You could also extract the integer part and then decimal part into two different columns.

[R-K-H]

Understood. But I am unable to use it in an INSERT query the database side is fine to handle as it's cast as NUMERIC, but the javascript side will not accept this as the text literal as passed.

Just for clarity:

const uuid = "c5e72e18-f3d7-446c-9c0e-243a26ac0176";
const balance_total = 0.02477774221871698;
const details = {};

const query = new Query(
"INSERT INTO balances (account_uuid, balance_total, details)
VALUES ($1::UUID, $2::NUMERIC, $3::JSONB)",
[uuid, balance_total, details]
);

No matter what I do to balance_total (set it to string, convert to BN) I am unable to get it to work with the above query.

Thanks for your help.

[malthe]

Lots of decimals there! Is this crypto? ;-)

I would advise you to avoid using floating point to represent money. It is better to use an integer – for USD this would be cents, for Bitcoin perhaps it would be a millionth of a Bitcoin.

[R-K-H]

Been using this data model for years and have over 33B records. Just trying to see if there is a work around for processing it using the library.

Numeric type has been more than accommodating for my use case.

[malthe]

Yes – on the PostgreSQL side it is (because you can specify the number of digits). But on the JavaScript side, you're stuck with a hybrid number type that has limited precision.

That said, you should be able to do $2::TEXT::NUMERIC – or you could try $2::FLOAT::NUMERIC.

[R-K-H]

Roger. Thanks for the tip. Is there any documentation for parsing it say by using BN.js or some sort. I've looked through the code and didn't see any way off the first glance.

Appreciate your help.

[malthe]

The problem is that we'd have to go through https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/numeric.c to figure out how to parse the numeric encoding.

That's not so difficult but is there a "canonical library" for doing numeric values in JavaScript? It could also be returned as a Number, or as [Number, Number] to improve accuracy.

Or as a string?

[R-K-H]

Nothing that I know of off the top of my head (and some searching), just some libraries that web3 frameworks etc are using for their support of large decimals for crypto.

https://github.com/indutny/bn.js/
https://mikemcl.github.io/decimal.js/

Those are the only two I've seen in 3rd party SDKs so far, a lot of structure around these types encoded on the platform / service side...

[malthe]

I suppose one option could be to always return unsupported types as text – this way it is rather straight-forward for the user to convert to some other value.

[gavbaa]

Adding support for doing something here, as built-in Postgres functions like AVG() return numeric as well, which means they get null'd out by default. Even if it's an opt-in setting on the driver "RETRIEVE_NUMERIC_AS" with settings of "text", "number" and maybe a default of null so someone has to intentionally turn it on, that would still provide better outcomes for users of this library.