Postgres临时表实例化方案求助及CSV导入问题排查

最优方案：直接在数据库内解析数据（无需临时文件） #

跳过文件导出导入的步骤，直接从repository表的data字段中拆分解析数据到临时表，避免文件权限和格式解析问题，更适合Grafana等SQL场景：

-- 创建临时表
CREATE TEMPORARY TABLE mygraphs (date date, value double precision);

-- 从存储的文本中解析数据并插入临时表
INSERT INTO mygraphs
SELECT
  trim(split_part(line, E'\t', 1), '"')::date AS date,
  split_part(line, E'\t', 2)::double precision AS value
FROM
  string_to_table((SELECT data FROM repository WHERE series_name = 'rampant_inflation'), E'\n') AS line
WHERE
  line <> ''; -- 过滤空行

-- Grafana可视化查询
SELECT date AS time, value FROM mygraphs ORDER BY time;

方案说明 #

• string_to_table将data字段中存储的多行文本按换行符拆分成单独的行记录
• split_part按制表符（E'\t'）拆分每行的两个字段
• trim(..., '"')去除日期字段前后的引号，再转为date类型
• 数值字段直接转double precision，PostgreSQL可以自动处理-2147483648这类边界值

若坚持使用COPY文件方案 #

如果必须通过临时文件中转，需要在导出和导入时严格指定格式参数，避免PostgreSQL自动添加额外的引号或转义：

1. 导出数据到临时文件 #

COPY (SELECT data FROM repository WHERE series_name = 'rampant_inflation') 
TO '/home/grafana/temp/mytemp.txt' 
FORMAT TEXT
DELIMITER E'\x00' -- 使用空字符作为字段分隔符，防止拆分data内容
QUOTE '' -- 禁用字段引号包裹
ESCAPE ''; -- 禁用转义字符

2. 创建临时表并导入 #

CREATE TEMPORARY TABLE mygraphs (date date, value double precision);

COPY mygraphs FROM '/home/grafana/temp/mytemp.txt'
FORMAT TEXT
DELIMITER E'\t'; -- 按制表符拆分字段

-- 去除日期字段的引号
UPDATE mygraphs SET date = trim(date::text, '"')::date;

原方案失败原因 #

• 默认COPY导出会给包含换行符的字段添加引号，导致导出的文件中每行被额外包裹，导入时被识别为单个字段
• CSV格式导出会自动转义内部引号，进一步破坏原始格式
• 直接导入时未处理日期字段的引号，导致PostgreSQL无法解析为合法日期

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