pg-promise returns integers as strings

Question

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?

Serrano

Answer 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.

Contemplation

I am having troubles using solution in UPDATE 2 of type Do not know how to serialize a BigInt while dealing with queries with COUNT(*). I see that simply typecasting in the query works fine. Something like 'SELECT count(*)::int as value .... Would you recommend not doing so? – Fancied

@Fancied If you are getting that error, that means you are using JSON.stringify on the data somewhere, which you absolutely should not. To serialize data that contains BigInt-s, use pgp.as.json, as explained in NigInt Manual. – Contemplation

Thank you @vitaly-t. After some trial and errors, I set the typeParsers correctly and used res.send(pgp.as.json(result, true)); in my express js application to send data from the server as JSON, and everything worked fine! – Fancied

Can you also write some notes about numeric columns? The lib returns string for it as well – April

Answer 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://mcmap.net/q/330998/-pg-promise-returns-integers-as-strings

Feminine

Which version of pg are you using? As far as I can see, it never exposes builtins there. – Contemplation

The information you provided is inaccurate. I have followed it up, and opened this issue. – Contemplation

"pg": "^7.11.0" (with typescript) – Feminine

It does not work with any existing version of pg. Not sure how you even tested it. See the issue I linked up above. – Contemplation

i have seen the issue, i already was using "pg-types": "^2.1.0" separately. And it was the same thing. And in my comment i mentioned (If for some reason pg.types.builtins is not accessible (in my case in typescript for some reason). ). Because that's what happen with me. I thought maybe it's just something related to my typescript config. cause in the pg-types builtins is well exported. exports.builtins = builtinTypes; in index ! – Feminine

@Contemplation i don't understand. What's inaccurate about what i provided ? – Feminine

Let us continue this discussion in chat. – Feminine

See UPDATE-2 added in my answer ;) – Contemplation

And added the Manual reference in that section now ;) – Contemplation

Answer 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.

Divergence

Not a good solution, modifying all queries for that, when you can achieve it for the entire project with a single line of code - pgp.pg.types.setTypeParser(20, parseInt) – Contemplation

Answer 4

just write following code

const { types } = require('pg');
types.setTypeParser(20, (val) => parseInt(val));

Luzon

Wrong library, bad example. – Contemplation