knex postgres returns strings for numeric/decimal values

Question

I have a table with column

table.decimal('some_column', 30,15) which on postgres is numeric(30,15)

When I run a knex.raw('select some_column from some_table') from node, the response I get in rows is like:

some_column: "5.000000000000000"
some_column: "10.000000000000000"

What really pointed me to this is that I do something like firstValue>lastValue I end up with a true response so that makes me think that these are returned as strings and not as numbers.

Any way to override this behavior?

If you are going to convert decimal number strings -> javascript numbers you must be extra careful with rounding errors. I would suggest to use some library like `decimal.js` or `big.js` if your application really needs to calculate with decimals and keep the values without rounding errors. — Mikael Lepistö, Aug 09 '17 at 08:52

score 10 · Answer 1 · edited Jun 20 '20 at 09:12

For an explanation about why and the possible solutions

check this great answer https://stackoverflow.com/a/39176670/7668448

You get the problem and the possible solutions.

Use of pg-types

Check my answer here: https://stackoverflow.com/a/57210469/7668448

resume:

All built in types:

const typesBuiltins = {
    BOOL: 16,
    BYTEA: 17,
    CHAR: 18,
    INT8: 20,
    INT2: 21,
    INT4: 23,
    REGPROC: 24,
    TEXT: 25,
    OID: 26,
    TID: 27,
    XID: 28,
    CID: 29,
    JSON: 114,
    XML: 142,
    PG_NODE_TREE: 194,
    SMGR: 210,
    PATH: 602,
    POLYGON: 604,
    CIDR: 650,
    FLOAT4: 700,
    FLOAT8: 701,
    ABSTIME: 702,
    RELTIME: 703,
    TINTERVAL: 704,
    CIRCLE: 718,
    MACADDR8: 774,
    MONEY: 790,
    MACADDR: 829,
    INET: 869,
    ACLITEM: 1033,
    BPCHAR: 1042,
    VARCHAR: 1043,
    DATE: 1082,
    TIME: 1083,
    TIMESTAMP: 1114,
    TIMESTAMPTZ: 1184,
    INTERVAL: 1186,
    TIMETZ: 1266,
    BIT: 1560,
    VARBIT: 1562,
    NUMERIC: 1700,
    REFCURSOR: 1790,
    REGPROCEDURE: 2202,
    REGOPER: 2203,
    REGOPERATOR: 2204,
    REGCLASS: 2205,
    REGTYPE: 2206,
    UUID: 2950,
    TXID_SNAPSHOT: 2970,
    PG_LSN: 3220,
    PG_NDISTINCT: 3361,
    PG_DEPENDENCIES: 3402,
    TSVECTOR: 3614,
    TSQUERY: 3615,
    GTSVECTOR: 3642,
    REGCONFIG: 3734,
    REGDICTIONARY: 3769,
    JSONB: 3802,
    REGNAMESPACE: 4089,
    REGROLE: 4096
};

Which you can find here https://github.com/brianc/node-pg-types/blob/master/lib/builtins.js

Use example:

const pg = require('pg');

pg.types.setTypeParser(pg.types.builtins.INT8, (value: string) => {
   return parseInt(value);
});

pg.types.setTypeParser(pg.types.builtins.FLOAT8, (value: string) => {
    return parseFloat(value);
});

pg.types.setTypeParser(pg.types.builtins.NUMERIC, (value: string) => {
    return parseFloat(value);
});

I think a better solution for INT8 is to set`pg.defaults.parseInt8 = true;` — Danil Valov, Jun 02 '21 at 07:50

score 4 · Accepted Answer · answered Aug 08 '17 at 13:47

You can take a look at pg-types module, which is used by pg module, which is used by knex, and configure parsing of your varialbes

var types = require('pg').types
types.setTypeParser(<I DONT REMEMBER VAR NAME, NEED TO CHECK>, value => value === null ? null : +value)

knex postgres returns strings for numeric/decimal values

2 Answers2

For an explanation about why and the possible solutions

Use of pg-types

resume:

Linked