如何从PostgreSQL的BYTEA列提取CSV数据并存入临时表？

1. 创建目标临时表 #

先创建与CSV数据结构匹配的临时表，字段类型需和CSV数据对应：

CREATE TEMP TABLE temp_csv_data (
    item_id INT,
    item_type_id INT,
    item_date DATE,
    item_value NUMERIC
);

2. 提取bytea列中的CSV数据并插入临时表 #

通过convert_from将bytea类型的二进制数据转为文本，拆分每行和字段后，过滤表头与空行再插入临时表：

INSERT INTO temp_csv_data (item_id, item_type_id, item_date, item_value)
SELECT
    split_part(line, ';', 1)::INT AS item_id,
    split_part(line, ';', 2)::INT AS item_type_id,
    split_part(line, ';', 3)::DATE AS item_date,
    split_part(line, ';', 4)::NUMERIC AS item_value
FROM (
    -- 将bytea转为文本，兼容Windows与Unix换行符并拆分每行
    SELECT unnest(string_to_array(
        replace(convert_from(f.file, 'UTF8'), E'\r\n', E'\n'),
        E'\n'
    )) AS line
    FROM file_upload f
    -- 可选：添加WHERE条件处理特定行，比如 WHERE f.id = 1
) AS raw_lines
-- 过滤空行和表头行
WHERE line <> '' 
  AND line NOT LIKE 'item_id;item_type_id;%';

关键说明 #

• 编码匹配：convert_from的第二个参数要和CSV文件实际编码一致，示例用UTF8，若为其他编码（如LATIN1）需替换。
• 换行兼容：用replace统一换行符格式，避免因Windows风格的\r\n拆分出无效空行。
• 单条数据处理：若只需提取file_upload表中某一行的CSV数据，在子查询中添加WHERE f.id = 目标ID即可。

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