PostgreSQL 9.6如何查找行中首个NULL列并实现精准更新？

针对你的加油站库存管理需求，结合PostgreSQL 9.6版本限制，这里提供一种简洁的实现方案，避免大量CASE WHEN嵌套：

核心思路 #

利用PostgreSQL 9.6支持的array_position函数，将tanknum1、tanknum2、tanknum3转为数组后，直接定位第一个NULL值的位置，再根据位置对应更新目标列；若tanknum3已非NULL则跳过更新。

单条更新语句 #

假设你要更新的数值为<目标值>，指定日期为'01/12/2023'，可执行以下语句：

UPDATE table1
SET 
    tanknum1 = CASE WHEN array_position(array[tanknum1, tanknum2, tanknum3], NULL) = 1 THEN <目标值> ELSE tanknum1 END,
    tanknum2 = CASE WHEN array_position(array[tanknum1, tanknum2, tanknum3], NULL) = 2 THEN <目标值> ELSE tanknum2 END,
    tanknum3 = CASE WHEN array_position(array[tanknum1, tanknum2, tanknum3], NULL) = 3 THEN <目标值> ELSE tanknum3 END
WHERE 
    workdate = to_date('01/12/2023', 'DD/MM/YYYY') -- 统一日期格式避免解析错误
    AND array_position(array[tanknum1, tanknum2, tanknum3], NULL) IS NOT NULL; -- 仅当存在NULL列时更新

批量处理.txt文件数据 #

如果数据来自外部.txt文件，可先将数据导入临时表，再关联更新：

1. 创建临时表并导入txt数据（假设文件以逗号分隔，首行为表头）：

CREATE TEMP TABLE temp_tanks (workdate date, tank_value integer);
COPY temp_tanks FROM '/path/to/your/data.txt' DELIMITER ',' CSV HEADER;

2. 关联原表批量更新：

WITH target_rows AS (
    SELECT 
        t1.id,
        array_position(array[t1.tanknum1, t1.tanknum2, t1.tanknum3], NULL) AS target_col,
        tt.tank_value
    FROM table1 t1
    JOIN temp_tanks tt ON t1.workdate = tt.workdate
    WHERE array_position(array[t1.tanknum1, t1.tanknum2, t1.tanknum3], NULL) IS NOT NULL
)
UPDATE table1
SET 
    tanknum1 = CASE WHEN tr.target_col = 1 THEN tr.tank_value ELSE tanknum1 END,
    tanknum2 = CASE WHEN tr.target_col = 2 THEN tr.tank_value ELSE tanknum2 END,
    tanknum3 = CASE WHEN tr.target_col = 3 THEN tr.tank_value ELSE tanknum3 END
FROM target_rows tr
WHERE table1.id = tr.id;

方案优势 #

• 无需多层嵌套的CASE WHEN判断，代码简洁易维护
• 利用数组函数统一处理列的顺序优先级，逻辑清晰
• 完全兼容PostgreSQL 9.6版本，无需升级数据库

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