10

When I run a query to my postgresql database on a node.js server, the value that I am getting is of variable type string when in fact it is a decimal in the postgresql database.

I am not sure why my decimals or even bigInts are returning as type strings. I am using knex as my ORM if that makes a difference. So far what I have read online is not very clear about what to do, and it seems as if this happens automatically to preserve precision???

What is the best work-around for this? Is it best to convert the string variable I am returned from my query into a decimal using parseFloat?

Thomas Gorczynski
  • 211
  • 3
  • 10
  • 1
    The best thing to do depends on what you want to do with the data. If you want to maintain the accuracy of the decimal or bigint value, you need to either leave it as string, or use some specialised data type. If you want to do maths and are happy to lose precision, you can convert to float. – IMSoP Mar 20 '17 at 11:06

1 Answers1

18

Both decimal and bigint types may contain values that are too large to "fit" in JavaScript's Number:

  • Number.MAX_SAFE_INTEGER (JS): 9007199254740991
  • bigint: -9223372036854775808 to 9223372036854775807
  • decimal: up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

If you're quite certain that the values in your database will fit in Number, you can convert them (I don't know Knex, but perhaps it has some sort of hook system that you can use to transform data that was retrieved from the database), or change your database schema to contain "smaller" row types.

Alternatively, there are also various "big integer" packages for Node that you might be able to use.

Lukasz Wiktor
  • 19,644
  • 5
  • 69
  • 82
robertklep
  • 198,204
  • 35
  • 394
  • 381
  • Awesome, thank you for the explanation! I'll be using [BigInteger.js](https://www.npmjs.com/package/biginteger.js), it looks like the easiest option :) – Thomas Gorczynski Mar 20 '17 at 14:57
  • 1
    How did you resolve the issue with non-integer (i.e. `decimal` type) numbers? – Kwhitejr Jan 01 '19 at 18:03
  • 1
    Probably you would have found the answer but this solves the decimal issue, just posting here for some one who comes later https://github.com/brianc/node-postgres/issues/811 – Qazi May 23 '19 at 01:11