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?

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.