如何在PostgreSQL中高效导入单个outflow格式TXT数据文件

问题背景 #

基于现有PostgreSQL表结构（包含scenarios、categories、data、outflow_values等表），需要在Debian命令行下将单个outflow类型的TXT文件（首行表头，第一列为日期，其余为Id_1~Id_n列，最多1102列、40000行数据）高效导入到outflow_values表中。

前提准备 #

1. 确认当前导入文件对应的scenario_name和category_name已存在于scenarios和categories表中（若不存在，先执行INSERT语句添加）。
2. 确保TXT文件的日期格式符合PostgreSQL的DATE类型要求（如YYYY-MM-DD，格式不符可后续转换）。

分步解决方案 #

步骤1：获取关联的scenario_id和category_id #

登录PostgreSQL命令行（psql -d 你的数据库名 -U 你的用户名），执行查询获取对应ID：

SELECT scenario_id FROM scenarios WHERE scenario_name = '目标场景名';
SELECT category_id FROM categories WHERE category_name = '目标状态名';

记下返回的两个ID，后续用SCENARIO_ID和CATEGORY_ID指代。

步骤2：预处理TXT文件，转换为可导入格式 #

原文件一行对应多个Id的数值，需拆分为日期、id_name、数值的单行记录格式。用awk工具处理：

# 替换为实际文件路径
INPUT_FILE="/path/to/your/outflow_file.txt"
FORMATTED_FILE="/path/to/formatted_outflow.txt"

awk -v sid=$SCENARIO_ID -v cid=$CATEGORY_ID '
NR==1 {
    # 提取表头中的所有Id列名
    for(i=2; i<=NF; i++) ids[i-1] = $i
    next
}
{
    # 将每行数据拆分为多条记录
    date = $1
    for(i=2; i<=NF; i++) {
        print date "\t" ids[i-1] "\t" $i
    }
}' $INPUT_FILE > $FORMATTED_FILE

步骤3：批量导入至数据库 #

回到psql命令行，执行以下SQL完成导入（替换占位符为实际值）：

-- 创建临时表存储预处理后的数据
CREATE TEMP TABLE temp_outflow (
    date DATE,
    id_name VARCHAR(50),
    value FLOAT
);

-- 导入预处理文件到临时表（替换文件路径）
\copy temp_outflow FROM '/path/to/formatted_outflow.txt' WITH (FORMAT TEXT, DELIMITER E'\t', HEADER FALSE);

-- 批量插入data表并关联插入outflow_values
WITH inserted_data AS (
    INSERT INTO data (date, scenario_id, category_id, id_name)
    SELECT date, 你的SCENARIO_ID, 你的CATEGORY_ID, id_name FROM temp_outflow
    RETURNING data_id, value
)
INSERT INTO outflow_values (data_id, value)
SELECT data_id, value FROM inserted_data;

-- 清理临时表
DROP TABLE temp_outflow;

一键脚本优化（可选） #

将上述步骤整合为shell脚本，方便重复执行：

#!/bin/bash

# 配置参数
SCENARIO_NAME="目标场景名"
CATEGORY_NAME="目标状态名"
INPUT_FILE="/path/to/outflow_file.txt"
FORMATTED_FILE="/path/to/formatted_outflow.txt"
DB_NAME="你的数据库名"
DB_USER="你的数据库用户名"

# 获取scenario_id和category_id
SCENARIO_ID=$(psql -d $DB_NAME -U $DB_USER -t -c "SELECT scenario_id FROM scenarios WHERE scenario_name = '$SCENARIO_NAME';" | xargs)
CATEGORY_ID=$(psql -d $DB_NAME -U $DB_USER -t -c "SELECT category_id FROM categories WHERE category_name = '$CATEGORY_NAME';" | xargs)

# 预处理文件
awk -v sid=$SCENARIO_ID -v cid=$CATEGORY_ID '
NR==1 {
    for(i=2; i<=NF; i++) ids[i-1] = $i
    next
}
{
    date = $1
    for(i=2; i<=NF; i++) {
        print date "\t" ids[i-1] "\t" $i
    }
}' $INPUT_FILE > $FORMATTED_FILE

# 执行数据库导入
psql -d $DB_NAME -U $DB_USER << EOF
CREATE TEMP TABLE temp_outflow (
    date DATE,
    id_name VARCHAR(50),
    value FLOAT
);

\copy temp_outflow FROM '$FORMATTED_FILE' WITH (FORMAT TEXT, DELIMITER E'\t', HEADER FALSE);

WITH inserted_data AS (
    INSERT INTO data (date, scenario_id, category_id, id_name)
    SELECT date, $SCENARIO_ID, $CATEGORY_ID, id_name FROM temp_outflow
    RETURNING data_id, value
)
INSERT INTO outflow_values (data_id, value)
SELECT data_id, value FROM inserted_data;

DROP TABLE temp_outflow;
EOF

# 清理临时文件
rm $FORMATTED_FILE

添加执行权限后运行：chmod +x import_outflow.sh && ./import_outflow.sh

注意事项 #

• 若日期格式不符合要求，可在\copy命令中添加DATEFORMAT '你的日期格式'参数（如DATEFORMAT 'MM/DD/YYYY'）。
• 使用客户端\copy命令无需PostgreSQL超级权限，适合普通用户操作；若用服务器端COPY命令，需确保文件在PostgreSQL可访问路径下。
• 批量导入方式相比逐行INSERT大幅提升效率，适配40000行级别的数据量。

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