使用Knex与Postgres为timestamp字段添加检查约束失败排查

问题描述 #

在Postgres后端使用Knex为两个timestamp字段添加检查约束时被拒绝，但date类型字段使用完全相同的约束逻辑可正常运行，唯一差异是date字段设置了notNull，timestamp字段未设置，怀疑是该差异导致问题？

可正常运行的date字段代码 #

t.date('startDate').notNull();
t.date('endDate').notNull();
t.check('?? < ??', ['startDate', 'endDate']);

无法运行的timestamp字段代码 #

// 已存在的字段
t.timestamp('resolvedAt');

// 新迁移代码
t.timestamp('fulfilledAt');
t.check('?? <= ??', ['resolvedAt', 'fulfilledAt'], 'fulfilledAt_check');

报错信息 #

migration failed with error: alter table "Order" add constraint
fulfilledAt_check check($1$2 <= $3$4) - syntax error at or near "$2"

问题原因与解决办法 #

报错源于Knex的check方法参数传递错误：当指定约束名称时，不能将其作为第三个独立参数，正确方式是把约束名称放入第二个参数的配置对象中。

修正后的基础代码 #

// 新迁移代码
t.timestamp('fulfilledAt');
t.check('?? <= ??', ['resolvedAt', 'fulfilledAt'], { constraintName: 'fulfilledAt_check' });

补充null场景处理 #

由于timestamp字段允许为null，原约束逻辑在字段为null时会触发违规（Postgres中null <= 任意值的结果为未知，会判定约束不满足），因此需要补充null场景的判断：

t.check(`(resolvedAt IS NULL OR fulfilledAt IS NULL OR resolvedAt <= fulfilledAt)`, { constraintName: 'fulfilledAt_check' });

该逻辑会在任意字段为null时通过约束，仅当两个字段都有值时才检查时间顺序。

内容的提问来源于stack exchange，提问作者kronosapiens