Geek asks and answers

COPY command: copy only specific columns from csv

I had a question related to a team COPYin PostgreSQL. I have a CSV file that I want to copy only some column values ​​into a PostgreSQL table.

Can this be done? I am familiar with using a command COPYto copy all data from a CSV to a table using a header to match column names, but how is this possible when I need only some columns?

+5
source share
1 answer

Either pre-process the CSV file, or (which I will probably do) import into a temporary copy of the target table, and INSERTonly the selected columns in the 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

. , .

+7

More articles:


All Articles