COPY 命令:仅从 csv 复制特定列 [英] COPY command: copy only specific columns from csv

问题描述

我对 PostgreSQL 中的 COPY 命令有疑问.我有一个 CSV 文件，我只想将一些列值复制到我的 PostgreSQL 表中.

I had a question surrounding the COPY command in PostgreSQL. I have a CSV file that I only want to copy some of the columns values into my PostgreSQL table.

可以这样做吗?我熟悉使用 COPY 命令将所有数据从 CSV 复制到使用标题映射到列名的表中，但是当我只想要一些列时，这怎么可能呢?

Is it possible to do this? I am familiar with using the COPY command to copy all of the data from a CSV into a table using the header to map to the column names but how is this possible when I only want some of the columns?

推荐答案

要么预处理 CSV 文件，要么(我可能会这样做)导入目标表的临时副本并 INSERT 在第二步中只选择了列:

Either pre-process the CSV file, or (what I probably would do) import into a temporary copy of the target table and INSERT only selected columns in a second step:

CREATE TEMP TABLE tmp AS SELECT * FROM target_table LIMIT 0;
ALTER TABLE tmp ADD COLUMN etra_column1 text
             ,  ADD COLUMN etra_column2 text;  -- add excess columns
COPY tmp FROM '/path/tp/file.csv';

INSERT INTO target_table (col1, col2, col3)
SELECT col1, col2, col3 FROM tmp  -- only reelvant columns
WHERE  ...  -- optional, to also filter rows

在会话结束时会自动删除一个临时表.如果处理时间较长，请使用常规表.

A temporary table is dropped automatically at the end of the session. If the processing takes longer, use a regular table.

这篇关于COPY 命令:仅从 csv 复制特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！