PostgreSQL使用PL/pgSQL通过record迁移数据遇NULL值语法错误

错误是因为将record直接拼接SQL字符串时，NULL值会被转换为空字符串，导致VALUES子句出现连续逗号（如,,），违反PostgreSQL语法规则。同时原代码还存在计数器未初始化、单条插入效率低、SQL注入风险等问题。

最优方案：直接使用INSERT...SELECT（推荐） #

这种方式无需循环，自动处理NULL值，性能远高于单条插入：

create or replace procedure data_movement_ref4(
    source_table  in varchar, 
    target_table in varchar, 
    p_minimum_date in timestamp, 
    p_end_date in timestamp 
) 
language plpgsql 
as $$ 
begin
    raise notice '最小日期: %', p_minimum_date;
    
    -- 用format转义表名，避免SQL注入和标识符错误
    execute format(
        'insert into %I select * from %I where your_date_column between %L and %L',
        target_table,
        source_table,
        p_minimum_date,
        p_end_date
    );
    
    -- 若数据量极大需分批提交，可使用以下批量插入逻辑
    /*
    declare
        cnt int := 0;
        batch_size int := 10000;
        c_cursor refcursor;
        recs record[];
    begin
        open c_cursor for execute format(
            'select * from %I where your_date_column between %L and %L', 
            source_table, 
            p_minimum_date, 
            p_end_date
        );
        
        loop
            fetch c_cursor into recs limit batch_size;
            exit when recs is null;
            
            execute format('insert into %I select * from unnest(%L)', target_table, recs);
            cnt := cnt + array_length(recs, 1);
            commit;
            raise notice '已迁移 % 条数据', cnt;
        end loop;
        
        close c_cursor;
        raise notice '迁移完成，共迁移 % 条数据', cnt;
    end;
    */
end; 
$$;

修复原循环方案（不推荐，仅作兼容参考） #

若必须保留循环逻辑，需修复NULL处理、计数器初始化等问题：

create or replace procedure data_movement_ref4(
    source_table  in varchar, 
    target_table in varchar, 
    p_minimum_date in timestamp, 
    p_end_date in timestamp 
) 
language plpgsql 
as $$ 
declare
    loop_rec record; 
    cnt int := 0; -- 初始化计数器
    c_cursor refcursor;
begin
    raise notice '最小日期: %', p_minimum_date;
    
    -- 用format转义表名，避免SQL注入
    open c_cursor for execute format('select * from %I', source_table);
    
    loop
        fetch c_cursor into loop_rec;
        exit when not found;
        
        -- 将record转为数组，处理每个NULL值为SQL的NULL关键字
        execute format(
            'insert into %I values (%s)',
            target_table,
            array_to_string(
                array(select coalesce(quote_literal(unnest(array[loop_rec.*])), 'NULL')),
                ','
            )
        );
        
        cnt := cnt + 1;
        if cnt = 10000 then
            commit;
            cnt := 0; -- 重置计数器
        end if;
    end loop;
    
    close c_cursor;
    -- 提交剩余未处理的数据
    if cnt > 0 then
        commit;
    end if;
    
    raise notice '迁移完成，共迁移 % 条数据', cnt;
end; 
$$;

• NULL值处理：通过coalesce(quote_literal(...), 'NULL')将空值转为PostgreSQL可识别的NULL关键字，避免VALUES子句出现连续逗号。
• SQL注入防护：使用format函数的%I占位符转义表名，防止恶意表名导致的注入风险。
• 计数器初始化：初始化cnt变量，避免未定义变量错误。
• 批量提交优化：批量提交后重置计数器，确保后续批次正常触发提交。

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