Skip to content
Vitaly Tomilov edited this page Nov 27, 2019 · 43 revisions

Node.js v10.4 made support for type BigInt official, and pg-promise v9.3 started supporting it.

This means that explicit type conversion from strings into something else by the client is no longer necessary (see this popular question), in either directions.

Receiving Data

In order to make the driver supply PostreSQL 64-bit types (such as BIGINT and BIGSERIAL) as BigInt, rather than strings, you need to set the following type parser:

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

test:

await db.one('SELECT 123::bigint as value'); //=> {value: 123n}

And if you are planning to execute queries that return an array of BigInt-s, you will need a separate type parser for that:

// 1016 = Type Id for arrays of BigInt values
const parseBigIntArray = pgp.pg.types.getTypeParser(1016);
pgp.pg.types.setTypeParser(1016, a => parseBigIntArray(a).map(BigInt));

test:

await db.one('SELECT ARRAY[1, 2]::bigint[] as value'); //=> {value: [1n, 2n]}

Sending Data

Query-formatting engine in pg-promise has been upgraded to support BigInt everywhere.

It means you can now supply BigInt as a formatting value for any query, and it is supported by all Formatting Filters.

example:

pgp.as.format('${this:csv}', {
    a: 123_456_789_012_345_678,
    b: 123_456_789_012_345_678n
});
//=> 123456789012345680,123456789012345678

The same large numerical above is rounded to 53 bits for number and to 64 bits for BigInt.

And when formatting data as JSON, with either JSON Filter, or directly via as.json function, any BigInt is formatted the same as number, but with the extra 11 bits of precision.

example:

const obj = {
    num1: 123_456_789_012_345_678, // regular number
    num2: 123_456_789_012_345_678n // BigInt number
};

pgp.as.json(obj); //=> '{"num1":123456789012345680,"num2":123456789012345678}'

It is important to know that the standard JSON.stringify cannot support BigInt as open values, and you need to use either JSON Filter or as.json for that.

Clone this wiki locally