如何正确编写在 pg-promise 中使用空约束的查询

Question

在编写Postgres查询时，约束通常写成 WHERE a = $(a)或WHERE b IN $(b:csv)，如果你知道这是一个列表。但是，如果一个值是null的，约束必须写WHERE x IS NULL。如果值为 null 或不是，是否可以让查询自动格式化？

假设我可能想查找WHERE c = 1行。如果我知道c是1，我会像这样写查询

db.oneOrNone(`SELECT * FROM blah WHERE c = $(c), { c })

但是如果c结果是null，查询将不得不变得...WHERE c IS NULL。

是否有可能构造一个像 WHERE $(c) 这样的通用查询，如果c 1，它会自动格式化为 WHERE c = 1，如果c设置为 null，它会自动格式化为 WHERE c IS NULL？

Answer 1

可以使用自定义类型格式来帮助处理动态查询：

const valueOrNull = (col, value) => ({
    rawType: true,
    toPostgres: () => pgp.as.format(`$1:name ${value === null ? 'IS NULL' : '= $2'}`,
                                    [col, value])
});

然后，您可以将其作为格式值传入：

db.oneOrNone('SELECT * FROM blah WHERE $[cnd]', { cnd: valueOrNull('col', 123) })

更新

或者，您可以仅对值本身使用自定义格式：

const eqOrNull = value => ({
    rawType: true,
    toPostgres: () => pgp.as.format(`${value === null ? 'IS NULL' : '= $1'}`, value)
});

使用示例：

db.oneOrNone('SELECT * FROM blah WHERE $1:name $2', ['col', eqOrNull(123)])
//=> SELECT * FROM blah WHERE "col" = 123
db.oneOrNone('SELECT * FROM blah WHERE $1:name $2', ['col', eqOrNull(null)])
//=> SELECT * FROM blah WHERE "col" IS NULL

请注意，为简单起见，我没有包括检查undefined，但您很可能会这样做，因为undefined内部也格式化为 null。

Answer 2

根据值是否NULL修改查询的一个非常有用的替代方法是使用 IS [NOT] DISTINCT FROM 。 参考资料：

对于非空输入，IS DISTINCT FROM与<>运算符相同。但是，如果两个输入都为 null，则返回 false，如果只有一个输入为 null，则返回 true。同样，对于非空输入，IS NOT DISTINCT FROM 与 = 相同，但当两个输入都为 null 时，它返回 true，当只有一个输入为 null 时返回 false。因此，这些谓词有效地充当 null 是正常数据值，而不是"未知"。

简而言之，与其使用=，不如使用IS NOT DISTINCT FROM，而不是<>，而是使用IS DISTINCT FROM。

这在比较两列（其中任何一列可能为 null）时变得特别有用。

请注意，IS [NOT] DISTINCT FROM不能使用索引，因此某些查询的性能可能很差。