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ö
– Mikael Lepistö, Commented Aug 9, 2017 at 8:52

Community · Accepted Answer · 2020-06-20 09:12:55Z

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 setpg.defaults.parseInt8 = true;

coockoo · Accepted Answer · 2017-08-08 13:47:50Z

6

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)

answered Aug 8, 2017 at 13:47

coockoo

2,40219 silver badges27 bronze badges

Collectives™ on Stack Overflow

knex postgres returns strings for numeric/decimal values

2 Answers 2

For an explanation about why and the possible solutions

Use of pg-types

resume:

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

For an explanation about why and the possible solutions

Use of pg-types

resume:

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related