pg-promise返回整数作为字符串 [英] pg-promise returns integers as strings

问题描述

我有一个简单的查询到包含bigint类型的列的表.

I have this simple query to a table that contains a column of type bigint.

但是，当我查询它时， pg-promise 将该列的值作为字符串返回.我在文档中找不到有关此信息.这是标准行为吗?

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采用以下形式，其中id作为字符串而不是int:

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

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

是否可以指示 pg-promise 返回实际类型?

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

推荐答案

以下是历史上积累的很多东西.但是，如果您使用的是Node.js v10.4.0或更高版本，则可以跳过所有步骤，然后跳到底部的UPDATE-2部分.

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.

这确实是标准行为.

bigint是64位，并且所有64位整数均由基础 node- postgres 驱动程序的类型为string，而32位驱动程序的返回类型为number.

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.

其原因是64位整数在JavaScript中没有确切的本机表示形式，它只能以一定的精度显示64位数字，并且不适合表示64位整数的整个范围数字.

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.

另请参阅:如何在Node中执行64位整数运算.js?

针对此问题，有三种解决方案，请选择一种最适合您的解决方案:

There are three possible solutions to this problem, pick up the one best suited for you:

解决方案1 ​​

请勿使用64位整数存储Id-s，如果预计您的表的记录不会超过40亿条，请改用默认的int类型，即32位，并将自动返回为整数.

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.

解决方案2

即时将返回的id转换为整数，但请记住，一旦id达到足够高的数字(53位)，转换后的值就会失真/更改.

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.

但是，您可以使用专门的库，该库可以将字符串正确地转换为64位整数(请参见上面的链接)，但是在查询中使用时可能会很尴尬.

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.

即时转换ID的示例:

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

请参见数据库.每个.

另一个示例是，记录计数始终以bigint的形式返回，因此获取这些记录的最佳方法是通过内联值转换+转换，如下所示:

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

请参见 Database.one .

解决方案3

您可以使基础 node-postgres 驱动程序忽略转换安全性并转换此类类型到处都是整数我不能说这是否是一个好主意，只能说可以通过pgp.pg.types.setTypeParser(...)轻松完成(请参见 pg类型):

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

当通过TypeScript使用pg-promise v9或更高版本时，可以使用以下代码替换上面的代码:

When using pg-promise v9 or later via TypeScript, you can replace the above code with this:

pgp.pg.types.setTypeParser(TypeId.INT8, parseInt);

---

请注意，解决方案2和3具有相同的作用，但是在两个不同的级别上:

---

Note that solutions 2 and 3 do the same thing, but on two different levels:

• 解决方案2中的本地转换
• 解决方案3中的隐式全局转换

UPDATE-2

版本9.3.0 增加了对本机 BigInt 类型的支持，如果您运行的是Node.js v10.4.0或更高版本，则现在可以使用.

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.

要使驱动程序自动使用 BigInt BIGINT + BIGSERIAL的a>:

To make the driver automatically use BigInt for BIGINT + BIGSERIAL:

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

有关更多详细信息，请参见项目WiKi中的 BigInt手册

For more details, see BigInt Manual in the project's WiKi.

这篇关于pg-promise返回整数作为字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！