Node-postgres: Numeric data type is returned as String

[petersirka]

Hi,
firstly thanks for this module, it's really great. I have found a problem in numeric data type. It's parsed as String.

Maybe I did something wrong or I have used bad DB type.
Thanks for any advice.

[bendrucker]

This actually does depend on your column type (http://www.postgresql.org/docs/9.4/static/datatype-numeric.html)

The type parsing here is handled by https://github.com/brianc/node-pg-types

If you dig into the readme, you'll see an example about int8 (64 bit). Those can't be parsed using parseInt because JS doesn't support 64 bit ints.

If you use a specific int type (smallint / 2 bytes, integer / 4 bytes) you'll get the Number you're expecting. But because numeric can be way larger than Number.MAX_SAFE_INTEGER, the only safe way to parse it is to return a string with full precision.

The good news is that if you know your numerics are within JS's range, you can override the parser as instructed over at pg-types. Or, even better, you can use a more restrictive column type and it'll happen automatically.

[petersirka]

@bendrucker thanks.

[super-cache-money]

@bendrucker

The good news is that if you know your numerics are within JS's range, you can override the parser as instructed over at pg-types

Any idea/pointer about how to tell whether my numerics are in this range? I'm storing currencies, up to 100 mill ( 100000000.00 ).

[super-cache-money]

It seems like they'll always be under the js maxint:

9007199254740992 is 16 numbers long and I only need 10^10 precision (10^8 + 2 for the decimal points - correct?).

I've got to manually force conversion though right? node-postgres won't realise it's safe and convert for me, even though I've declared a numeric(10,2), will it?

[illz]

Numeric type is oid 1700, so you can add this in your app.js or similar:

var types = require('pg').types
types.setTypeParser(1700, function(val) {
    return parseFloat(val);
});