postgres如何将excel文件中前n列的内容批量拆入到一张a表，后n列转行插入另一张b表

在PostgreSQL中，将Excel文件的前n列插入a表，后n列转行插入b表，可以通过以下几种方式实现：
方法1：使用pgAdmin或DBeaver的导入功能 + SQL处理
步骤1：准备Excel文件
将Excel保存为CSV格式，或确保列顺序正确。
步骤2：创建临时表并导入数据
-- 创建临时表包含所有列
CREATE TEMPORARY TABLE temp_import (
id SERIAL,
col1 VARCHAR,
col2 VARCHAR,
-- 前n列...
col_n VARCHAR,
col_n1 VARCHAR, -- 后n列开始
col_n2 VARCHAR,
-- 后n列...
col_2n VARCHAR
);

-- 使用COPY命令导入（需要CSV格式）
COPY temp_import(col1, col2, ..., col_2n)
FROM '/path/to/your/file.csv' DELIMITER ',' CSV HEADER;
步骤3：分别插入到目标表
-- 插入前n列到a表
INSERT INTO a_table (col1, col2, ..., col_n)
SELECT col1, col2, ..., col_n
FROM temp_import;

-- 插入后n列到b表（转行）
INSERT INTO b_table (foreign_key_id, column_name, column_value)
SELECT
ti.id as foreign_key_id,
unnest(ARRAY['col_n1', 'col_n2', ..., 'col_2n']) as column_name,
unnest(ARRAY[ti.col_n1, ti.col_n2, ..., ti.col_2n]) as column_value
FROM temp_import ti;
方法2：使用Python + psycopg2批量处理
import pandas as pd
import psycopg2
from psycopg2.extras import execute_values

连接数据库

conn = psycopg2.connect(
host="localhost",
database="your_db",
user="your_user",
password="your_password"
)
cur = conn.cursor()

读取Excel文件

df = pd.read_excel('your_file.xlsx')

定义前n列和后n列

n = 3 # 假设前3列插入a表，后3列转行插入b表
front_columns = df.columns[:n]
back_columns = df.columns[n:2*n]

插入前n列到a表

a_table_data = df[front_columns].to_dict('records')
execute_values(
cur,
"INSERT INTO a_table (col1, col2, col3) VALUES %s",
[tuple(record.values()) for record in a_table_data]
)

获取刚插入的ID（如果有自增ID）

cur.execute("SELECT lastval() - %s + 1, lastval()", (len(a_table_data),))
start_id, end_id = cur.fetchone()

插入后n列到b表（转行）

b_table_data = []
for idx, row in df.iterrows():
a_table_id = start_id + idx # 对应的a表ID
for col_name in back_columns:
if pd.notna(row[col_name]): # 跳过空值
b_table_data.append((a_table_id, col_name, row[col_name]))

execute_values(
cur,
"INSERT INTO b_table (a_id, column_name, value) VALUES %s",
b_table_data
)

conn.commit()
cur.close()
conn.close()
方法3：使用PostgreSQL的file_fdw扩展
步骤1：创建文件外部表
-- 启用扩展
CREATE EXTENSION IF EXISTS file_fdw;

-- 创建服务器
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;

-- 创建外部表
CREATE FOREIGN TABLE csv_import (
col1 VARCHAR,
col2 VARCHAR,
-- 所有列...
col_2n VARCHAR
) SERVER csv_server
OPTIONS (filename '/path/to/your/file.csv', format 'csv', header 'true');
步骤2：使用SQL进行数据拆分和转换
-- 插入a表
INSERT INTO a_table (col1, col2, ..., col_n)
SELECT col1, col2, ..., col_n FROM csv_import;

-- 插入b表（使用横向连接转行）
INSERT INTO b_table (a_id, col_name, col_value)
WITH a_ids AS (
SELECT ctid, col1, col2, ..., col_n
FROM csv_import
)
SELECT
ai.ctid,
kv.col_name,
kv.col_value
FROM a_ids ai
CROSS JOIN LATERAL (
VALUES
('col_n1', ai.col_n1),
('col_n2', ai.col_n2),
-- ... 所有后n列
('col_2n', ai.col_2n)
) AS kv(col_name, col_value);
方法4：使用COPY命令 + 字符串处理
-- 创建临时表
CREATE TEMP TABLE raw_data (line TEXT);

-- 导入原始数据
COPY raw_data FROM '/path/to/file.csv';

-- 使用正则表达式拆分（假设逗号分隔）
WITH parsed AS (
SELECT
regexp_split_to_array(line, ',') as cols
FROM raw_data
WHERE line !~ '^[1]*$' -- 跳过空行
)
INSERT INTO a_table (col1, col2, col3)
SELECT
cols[1] as col1,
cols[2] as col2,
cols[3] as col3
FROM parsed;

-- 插入b表
WITH parsed AS (
SELECT
row_number() over () as rn,
regexp_split_to_array(line, ',') as cols
FROM raw_data
WHERE line !~ '^[2]*$'
)
INSERT INTO b_table (a_id, col_name, value)
SELECT
p.rn,
unnest(ARRAY['col4', 'col5', 'col6']),
unnest(ARRAY[cols[4], cols[5], cols[6]])
FROM parsed p;
推荐方案
小数据量：使用方法1，直接在数据库内处理
大数据量或复杂转换：使用方法2，Python处理更强大
需要定期导入：使用方法3，创建外部表便于重复使用
选择哪种方法取决于你的具体需求、数据量大小和技术栈偏好。

---

1. [:space:] ↩︎

2. [:space:] ↩︎