pg-promise returns integers as strings

I have this simple query to a table that contains a column of type bigint.

However when I query it, pg-promise returns this column's values as a string. I can't find info about that in the documentation. Is that standard behavior?

var ids = [180, 120];

db.any('SELECT id_brand, brand from catalog_brand WHERE id_brand in ($1:csv)', [ids])
    .then((data) => {
        // return results
    });

data takes the following form, with id as string instead of int:

[{id_brand: "180", brand: "Ford"}, {id_brand: "120", brand: "Nike"}]

Is there anything to instruct pg-promise to return the actual type?

Solution 1:

There is a lot below that's been accumulating historically. But if you are using Node.js v10.4.0 or later, you can skip all this, and jump into section UPDATE-2 at the bottom.

This is indeed the standard behavior.

bigint is 64-bit, and all 64-bit integers are returned by the underlying node-postgres driver as type string, while 32-bit ones are returned as number.

The reason for this is that a 64-bit integer doesn't have the exact native presentation in JavaScript, which can only present 64-bit numbers with certain precision, and that's not suitable for representing the full range of 64-bit numbers.

See also: How to do 64bit Integer arithmetic in Node.js?

There are three possible solutions to this problem, pick up the one best suited for you:

Solution 1

Do not use 64-bit integers to store Id-s, if your table isn't expected to ever have more than 4 billion records, use the default int type instead, which is 32-bit, and will be returned as an integer automatically.

Solution 2

Convert the returned id-s into integers on-the-fly, but keep in mind that once your id-s reach numbers high enough (53 bits), the converted values will become distorted / changed.

You can, however, use a specialized library that can properly convert a string into a 64-bit integer (see the link above), but that can be awkward to use across queries.

Example of converting your id-s on-the-fly:

db.each('SELECT id_brand FROM catalog_brand WHERE id_brand in ($1:csv)', [ids], cat=> {
    cat.id_brand = parseInt(cat.id_brand)
})
    .then(rows => {
        // id_brand is now an integer in each row
    });

See Database.each.

As another example, record counts are always returned as bigint, so the best way to get those is through in-line value transformation + conversion, like this:

db.one('SELECT count(*) FROM catalog_brand', [], c => +c.count)
    .then(count => {
        // count = a proper integer value, rather than an object with a string
    });

See Database.one.

Solution 3

You can make the underlying node-postgres driver disregard the conversion safety and convert such types into integers everywhere. I can't say if it is a good idea in general, only that it can be done easily, via pgp.pg.types.setTypeParser(...) (see pg-types):

// Convert bigserial + bigint (both with typeId = 20) to integer:
pgp.pg.types.setTypeParser(20, parseInt);

UPDATE-1

When using pg-promise v9 or later via TypeScript, you can replace the above code with this:

pgp.pg.types.setTypeParser(TypeId.INT8, parseInt);

Note that solutions 2 and 3 do the same thing, but on two different levels:

• explicit local conversion in solution 2
• implicit global conversion in solution 3

UPDATE-2

Version 9.3.0 of the library added support for the native BigInt type, which now you can use, if you are running Node.js v10.4.0 or later.

To make the driver automatically use BigInt for BIGINT + BIGSERIAL:

pgp.pg.types.setTypeParser(20, BigInt); // Type Id 20 = BIGINT | BIGSERIAL

For more details, see BigInt Manual in the project's WiKi.

Solution 2:

@vitaly-t answer explain everything!

For implicit global conversion on postgree (solution 3) of @vitaly-t answer.

Here what you need to know:

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

Normaly you can access it this way

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);
});

That normaly will handle all numeric data.

If for some reason pg.types.builtins is not accessible (in my case in typescript for some reason). You can just copy past it. Or use the corresponding mapped number directly.

update (to avoid confusion)

As by now "pg": "^7.11.0". pg is using pg-types 2.0.1 which doesn't includes builtins at all. And so does all the version before. This lead to the access pg.types.builtins. being not viable (in any of the version up to the one mentioned).

The solutions as i mentioned before is to copy past the mapping as i did in my current project. (check the snippet all above to copy it)

Or to use the corresponding mapping directly given the list.

pgp.pg.types.setTypeParser(20, parseInt);

Another solution as a workaround is to use the pg-types package directly. In it's latest version.

const types = require('pg-types');
// types.builtins.INT8

Otherwise a PR is filled by @vitaly-t which can be seen in the link bellow:
https://github.com/brianc/node-postgres/pull/1937/commits/c7666214833715ac2494b81865cfe1ea7cef9289

Which update the version of pg-types withing pg package (node-postgres) .

So once it's accepted. The initials examples will start working.

Note that my source initially was the official README of pg-types:
https://github.com/brianc/node-pg-types

 Another and last note:

This one concern the use of typescript.

pg-types typescript typing doesn't include builtins as with the current version "pg-types": "^2.1.0". It's to be updated. So you either you add the typing yourself.

typeof types & {builtins: {[key in builtinsTypes]: number}}

where builtinsTypes is the union of all the properties names.

(however i just find copy pasting the hole object faster, shorter and cleaner).

You can do that with an enum as bellow

enum TypeId {
        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
}

as done within pg-promise https://github.com/vitaly-t/pg-promise/blob/v9/typescript/pg-subset.d.ts#L103

Once everything updated. The use from pg is the way to go.

Update

The package was updated. And you can use it as expected.

import { types } from 'pg';

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

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

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

Also go check part UPDATE-2 of the above vitaly-t answer https://stackoverflow.com/a/39176670/7668448

Solution 3:

Another alternative, specifically if you're using JavaScript without BigInt support, is to cast the value to an int in the SQL query, as such:

var ids = [180, 120];

// Cast id_brand to an int to ensure it is not parsed as a string.
db.any('SELECT id_brand::int, brand from catalog_brand WHERE id_brand in ($1:csv)', [ids])
    .then((data) => {
        // return results
    });

Of course, this does not help you if id_brand has values larger than the maximum int.