Postgres 9.3中NULL字段自增更新失效的原因及解决方法

为什么NULL时UPDATE不生效？ #

这事儿核心是PostgreSQL里NULL的运算特性导致的：在PostgreSQL中，NULL和任何值进行算术运算的结果依然是NULL。当members字段为NULL时，执行members = members + 1其实等价于NULL = NULL + 1，最终结果还是NULL——看起来就像UPDATE没生效，但实际上字段值确实被更新成了NULL（和原来一样）。

而当members是整数值时，常规的整数加法逻辑生效，自然能正常自增1。

解决办法 #

这里有两种常用的方案，你可以根据场景选择：

方案1：用COALESCE函数处理NULL值

直接在UPDATE语句里把NULL转换成0再做加法，这样不管字段当前是NULL还是整数，都能正确自增：

UPDATE table_statatistic SET members = COALESCE(members, 0) + 1 WHERE user_id = $1;

COALESCE函数的作用是返回参数列表里第一个非NULL的值，所以如果members是NULL，就用0代替，再执行+1操作。

方案2：给字段设置默认值+批量修正现有NULL

如果希望从根源上避免这个问题，可以给members字段设置默认值0，这样新插入的行默认就是0，不会出现NULL的情况：

-- 先给字段设置默认值0
ALTER TABLE table_statatistic ALTER COLUMN members SET DEFAULT 0;
-- 再把已有的NULL行批量更新为0
UPDATE table_statatistic SET members = 0 WHERE members IS NULL;

之后再执行原来的UPDATE语句，就不会有NULL导致的问题了。

补充说明 #

虽然你用的是PostgreSQL 9.3（版本确实有点老了，建议有机会升级），但NULL的运算规则是PostgreSQL的核心特性之一，所有版本都遵循这个逻辑，所以上面的方案在9.3里完全适用。

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