如何将Reports文件夹中规则命名的UTF-8文本内容导入Postgres表？

1. 先创建目标表 #

首先执行SQL创建存储用户报告的表：

CREATE TABLE user_reports (
    user_code VARCHAR(20) PRIMARY KEY,
    report TEXT NOT NULL
);

2. 方法一：使用Postgres内置函数直接读取文件 #

适合有权限操作Postgres数据目录的场景：

前置准备

• 将Reports文件夹移动到Postgres的数据目录下（可通过SHOW data_directory;查询路径），或确保Postgres进程对Reports目录有读取权限
• 给执行SQL的用户授予读取服务器文件的权限：

GRANT pg_read_server_files TO your_postgres_user;

执行导入SQL

这条SQL会遍历Reports目录下的所有txt文件，提取文件名作为user_code，读取文件内容作为report：

INSERT INTO user_reports (user_code, report)
SELECT
    REPLACE(filename, '.txt', '') AS user_code,
    pg_read_file('Reports/' || filename) AS report
FROM
    pg_ls_dir('Reports') AS filename
WHERE
    filename LIKE '%.txt';

如果存在重复的user_code，可以用ON CONFLICT子句处理：

INSERT INTO user_reports (user_code, report)
SELECT
    REPLACE(filename, '.txt', '') AS user_code,
    pg_read_file('Reports/' || filename) AS report
FROM
    pg_ls_dir('Reports') AS filename
WHERE
    filename LIKE '%.txt'
ON CONFLICT (user_code) DO UPDATE 
    SET report = EXCLUDED.report; -- 覆盖已有记录，或用DO NOTHING跳过

3. 方法二：Shell脚本批量生成插入语句 #

无需调整Postgres目录权限，灵活度更高：

创建import_reports.sh脚本：

#!/bin/bash

# 配置数据库参数
DB_NAME="your_database_name"
DB_USER="your_postgres_user"
REPORTS_DIR="./Reports" # 替换为你的Reports文件夹路径

# 遍历所有txt文件生成INSERT语句
for file in "$REPORTS_DIR"/*.txt; do
    # 提取user_code（去掉.txt后缀）
    user_code=$(basename "$file" .txt)
    # 转义内容中的单引号，避免SQL语法错误
    report_content=$(sed "s/'/''/g" "$file")
    # 输出INSERT语句
    echo "INSERT INTO user_reports (user_code, report) VALUES ('$user_code', '$report_content');"
done | psql -d "$DB_NAME" -U "$DB_USER"

赋予脚本执行权限并运行：

chmod +x import_reports.sh
./import_reports.sh

4. 方法三：用COPY命令高效导入 #

适合处理大量文件的场景，导入速度更快：

# 生成user_code|report格式的数据，通过管道传给Postgres的COPY命令
find ./Reports -name "*.txt" -exec sh -c 'echo -n "$(basename "{}" .txt)|"; cat "{}"' \; | psql -d your_database -U your_user -c "COPY user_reports (user_code, report) FROM STDIN DELIMITER '|' QUOTE E'\\000' NULL '';"

这里用空字符作为引号分隔符，避免文件内容里的引号或特殊字符破坏数据格式。

注意事项 #

• 确保所有txt文件都是UTF-8编码，若不是，可先用iconv转码：iconv -f 原编码 -t utf-8 input.txt > output.txt
• 若文件内容包含大量特殊字符，优先使用方法一或方法三，避免Shell转义出错
• 执行操作前建议先备份数据库或测试少量文件，确保数据导入正确

内容的提问来源于stack exchange，提问作者Jose Cabrera Zuniga