COPY command: copy only specific columns from csv

Question

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.

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?

Solution 1

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.

Solution 2

COPY target_table FROM PROGRAM 'cut -f1,2,3 -d, /path/tp/file.csv';

Comments

[parchambeau]

Okay awesome, this is great info. I was thinking of pre processing the CSV file but the temp table seems like it will be much easier. Thank you!

[Chloe]

Any tips if the table is huge - gigabyte size?

[Erwin Brandstetter]

@Chloe: It very much depends on the details and your requirements. Your version of Postgres, size of table and CSV file, width of the row, how many excess columns, available resources, superuser privileges, concurrent access, data in CSV consistent? Etc. I suggest you start a new question with the specifics. thanks for the edit, BTW, I was indeed focused on selecting rows instead of columns.

[smtlaissezfaire]

^ the cut command above can help you preprocess the data, as suggested above.