如何批量将多份CSV文件导入PostgreSQL对应独立表？

你提供的逻辑代码存在几个问题：pg_ls_dir2不是PostgreSQL的内置函数（标准函数是pg_ls_dir），循环和COPY的语法不符合SQL规范，而且动态生成表和导入的逻辑需要用PL/pgSQL（PostgreSQL的过程化语言）实现。以下是两种可行的解决方案：

方案一：用PL/pgSQL服务器端批量处理（适合文件在PostgreSQL服务器上） #

前置准备 #

1. 确保PostgreSQL服务器进程（通常是postgres用户）能读取CSV文件夹，给文件夹设置正确权限：

   chown -R postgres:postgres /path/to/your/csv/folder
   chmod -R 755 /path/to/your/csv/folder
   

2. 先创建一个模板表（因为所有CSV列属性相同）：
   • 任选一个CSV文件，手动导入生成模板表结构：

     -- 替换路径和文件名
     CREATE TABLE template_table AS
     SELECT * FROM csvread('/path/to/your/csv/folder/sample.csv');
     

   • 或者手动定义列结构后导入样本验证：

     CREATE TABLE template_table (
         -- 根据你的CSV列定义结构，示例：
         id INT,
         name TEXT,
         value NUMERIC
     );
     -- 导入样本数据验证结构
     COPY template_table FROM '/path/to/your/csv/folder/sample.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');
     

执行批量导入的PL/pgSQL匿名块 #

DO $$
DECLARE
    filename TEXT;
    table_name TEXT;
    csv_root_path TEXT := '/path/to/your/csv/folder/'; -- 替换为你的服务器端CSV路径
BEGIN
    -- 遍历目录下所有.csv后缀的文件
    FOR filename IN 
        SELECT pg_ls_dir(csv_root_path) 
        WHERE pg_ls_dir(csv_root_path) ~ '\.csv$' -- 匹配.csv结尾的文件
    LOOP
        -- 生成合法表名：去掉.csv后缀，转义特殊字符（比如空格、连字符）
        table_name := quote_ident(replace(filename, '.csv', ''));
        
        -- 创建与模板表结构完全一致的新表（如果已存在则跳过）
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %s (LIKE template_table INCLUDING ALL)',
            table_name
        );
        
        -- 导入CSV数据到对应表
        EXECUTE format(
            'COPY %s FROM %L WITH (FORMAT csv, HEADER true, DELIMITER '', '')',
            table_name,
            csv_root_path || filename
        );
        
        -- 打印进度提示
        RAISE NOTICE '已完成导入：表 % <- 文件 %', table_name, filename;
    END LOOP;
END $$;

方案二：用Shell脚本客户端批量处理（适合文件在本地电脑） #

如果CSV文件在你的本地机器（而非PostgreSQL服务器），用Shell脚本结合psql的\copy命令更方便：

#!/bin/bash

# 配置参数
CSV_DIR="/path/to/your/local/csv/folder"
DB_NAME="your_database_name"
DB_USER="your_database_user"
DB_HOST="localhost" # 替换为你的数据库主机
DB_PORT="5432"      # 替换为你的数据库端口
TEMPLATE_TABLE="template_table" # 提前创建好的模板表名

# 遍历所有CSV文件
for file in "$CSV_DIR"/*.csv; do
    # 生成表名：去掉文件路径和.csv后缀
    raw_table_name=$(basename "$file" .csv)
    # 转义表名避免特殊字符导致SQL错误
    table_name=$(psql -d "$DB_NAME" -U "$DB_USER" -h "$DB_HOST" -p "$DB_PORT" -t -c "SELECT quote_ident('$raw_table_name');")
    table_name=$(echo "$table_name" | xargs) # 去除多余空格
    
    # 创建新表（复制模板表结构）
    psql -d "$DB_NAME" -U "$DB_USER" -h "$DB_HOST" -p "$DB_PORT" -c "CREATE TABLE IF NOT EXISTS $table_name (LIKE $TEMPLATE_TABLE INCLUDING ALL);"
    
    # 执行客户端导入（\copy是客户端命令，读取本地文件）
    psql -d "$DB_NAME" -U "$DB_USER" -h "$DB_HOST" -p "$DB_PORT" -c "\copy $table_name FROM '$file' WITH (FORMAT csv, HEADER true, DELIMITER ',');"
    
    echo "导入完成：$file -> $table_name"
done

关键注意事项 #

• 权限问题：服务器端方案需要postgres用户能访问CSV文件夹；客户端方案需要psql能正常连接数据库，且用户有创建表和插入数据的权限。
• CSV格式适配：根据你的实际CSV文件调整COPY参数，比如分隔符是;就改成DELIMITER ';'，没有表头就去掉HEADER true。
• 错误处理：如果需要跳过导入失败的文件，可以在PL/pgSQL中添加EXCEPTION块，或者在Shell脚本中加入|| continue。

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