Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

knex postgres returns strings for numeric/decimal values

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?

like image 888
Alin Avatar asked Jan 04 '23 15:01

Alin


1 Answers

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);
});
like image 121
Mohamed Allal Avatar answered Jan 15 '23 09:01

Mohamed Allal