PostgreSQL动态SQL创建年月命名表并插入数据问题

我之前也踩过这个动态SQL表名拼接的坑！问题出在PostgreSQL对标识符（表名、列名等）的解析规则上，直接字符串拼接很容易导致语法错误，或者遇到特殊字符时出问题，用format()函数配合%I占位符才是正确的打开方式。

完整解决方案（用DO块快速执行） #

如果只是临时执行，用DO匿名块就能搞定，不需要创建函数：

DO $$
DECLARE
    -- 生成目标表名：test_ + 当前年月（比如test_202409）
    target_table text := 'test_' || to_char(CURRENT_DATE, 'yyyymm');
BEGIN
    -- 1. 创建不存在的表（用%I安全处理表名）
    EXECUTE format('
        CREATE TABLE IF NOT EXISTS %I (
            id SERIAL PRIMARY KEY,
            content TEXT NOT NULL,
            created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ', target_table);

    -- 2. 插入数据（用USING传递参数，避免SQL注入）
    EXECUTE format('
        INSERT INTO %I (content) VALUES ($1)
    ', target_table) USING '这是一条测试数据';
END $$;

关键细节解释 #

1. 表名生成：
   用to_char(CURRENT_DATE, 'yyyymm')精准获取当前年月的字符串格式，拼接到test_后面，确保表名完全符合test_yyyymm的要求。

2. 为什么用format()和%I？

   • %I会自动将表名转换为PostgreSQL合法的标识符：如果表名包含特殊字符（比如大小写混合、空格），它会自动添加双引号包裹；即使是普通小写表名，这也是最安全的写法，避免语法解析错误。
   • 直接字符串拼接（比如'CREATE TABLE ' || target_table）虽然偶尔能工作，但一旦表名生成逻辑有变动（比如不小心引入特殊字符），就会触发语法错误，而且存在SQL注入风险。

3. 插入数据的正确姿势：
   插入数据时用USING子句传递参数，而不是把值直接拼到SQL字符串里，这既安全又能避免类型转换错误。

封装成函数（适合重复调用） #

如果需要定期执行这个操作，可以封装成函数：

CREATE OR REPLACE FUNCTION create_monthly_test_table(insert_content TEXT)
RETURNS void AS $$
DECLARE
    target_table text := 'test_' || to_char(CURRENT_DATE, 'yyyymm');
BEGIN
    -- 创建表（如果不存在）
    EXECUTE format('
        CREATE TABLE IF NOT EXISTS %I (
            id SERIAL PRIMARY KEY,
            content TEXT NOT NULL,
            created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ', target_table);

    -- 插入自定义内容
    EXECUTE format('
        INSERT INTO %I (content) VALUES ($1)
    ', target_table) USING insert_content;
END $$ LANGUAGE plpgsql;

-- 调用示例
SELECT create_monthly_test_table('来自函数的测试数据');

这样每次调用函数，就能自动创建当月的表并插入指定数据了。

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