PostgreSQL动态文件名备份表报错：语法错误在"||"附近

错误原因 #

PostgreSQL的静态COPY语句不支持动态拼接文件名，TO关键字后只能指定字符串常量，你用||拼接字符串的写法不符合静态SQL的语法规则，因此触发语法错误。

解决方案 #

使用PL/pgSQL的动态SQL来构造COPY语句，通过字符串拼接生成带动态文件名的命令，再执行该命令。同时注意格式化日期字符串，避免生成包含冒号这类Windows路径非法字符的文件名。

修改后的完整代码 #

DO $$
DECLARE
    v_start_date timestamp;
    v_end_date timestamp;
    v_file_path text;
BEGIN
    -- 计算备份日期范围并存储到变量
    SELECT
        (date_trunc('day', now() - interval '30 days'))::timestamp,
        (date_trunc('day', now()))::timestamp - interval '1 second'
    INTO v_start_date, v_end_date;

    -- 构造带日期的文件路径，用YYYYMMDD格式避免非法字符
    v_file_path := 'C:\\Users\\Desktop\\database\\backup_' || to_char(v_start_date, 'YYYYMMDD') || '.json';

    -- 执行动态COPY命令
    EXECUTE format('COPY (
        SELECT *
        FROM mqtt_table
        WHERE created_at < %L OR created_at >= %L
    ) TO %L WITH (FORMAT json)', v_start_date, v_end_date, v_file_path);

    -- 删除备份的记录
    DELETE FROM mqtt_table
    WHERE created_at < v_start_date OR created_at >= v_end_date;
END $$;

关键说明 #

• 用DO匿名块执行PL/pgSQL逻辑，无需创建持久化函数
• 借助format()函数安全构造动态SQL，%L会自动处理字符串转义，避免SQL注入风险
• 通过to_char()将日期格式化为无特殊字符的字符串，确保Windows路径合法
• 直接将日期范围存储到变量，避免重复查询临时表，提升效率

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