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

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

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

---

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