Antoine Antoine - 11 months ago 101
Node.js Question

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 string. I can't find info about that in the doc. Is that standard behavior?

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

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

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

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

Answer Source

This is indeed the standard behavior.

bigint is 64-bit, and all 64-bit integers are returned by the underlying node-postgres driver as strings.

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?

So, the solution to your problem can have two different approaches...

Solution 1

Do not use 64-bit integers to store Id-s, 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. The problem with this approach, 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 when implementing queries.

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

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

See Database.each.

Alternatively, you can make the underlying node-postgres driver disregard the conversion safety and convert such types into integers anyway. Perhaps this is a bad idea, but it can be done, via, see pg-types.