PostgreSQL根据整数列fish_age更新二进制列的UPDATE语句写法

修正规则 #

• 整数列fish_age非NULL时，二进制列fish_otolith置为1
• 整数列fish_age为NULL时，二进制列fish_otolith置为0

实现语句 #

简洁写法（PostgreSQL 原生支持） #

利用PostgreSQL布尔值转整数时TRUE→1、FALSE→0的特性，写法最精简：

-- 把your_table替换成你的实际表名即可
UPDATE your_table
SET fish_otolith = (fish_age IS NOT NULL)::int;

通用兼容写法 #

用标准SQL的CASE语句实现，逻辑直观，跨数据库版本也能运行：

UPDATE your_table
SET fish_otolith = CASE
    WHEN fish_age IS NOT NULL THEN 1
    ELSE 0
END;

大表优化写法 #

如果表数据量较大，可增加过滤条件，仅更新取值错误的记录，减少不必要的行锁和磁盘IO：

UPDATE your_table
SET fish_otolith = (fish_age IS NOT NULL)::int
WHERE fish_otolith IS DISTINCT FROM (fish_age IS NOT NULL)::int;

这里用IS DISTINCT FROM可以兼容fish_otolith本身为NULL的场景，不会漏判需要更新的行。

执行建议 #

正式执行更新前建议先开启事务验证，确认结果符合预期后再提交，避免误改：

BEGIN;
-- 执行更新语句
UPDATE your_table SET fish_otolith = (fish_age IS NOT NULL)::int;
-- 抽样查询校验结果，可分别查询age为空、非空的样本核对
SELECT fish_age, fish_otolith FROM your_table LIMIT 20;
-- 校验正确执行COMMIT提交，发现问题执行ROLLBACK回滚即可
COMMIT;

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