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屋!