10

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
parchambeau
  • 1,141
  • 9
  • 34
  • 56

2 Answers2

11

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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! – parchambeau Mar 29 '13 at 17:34
  • 1
    Any tips if the table is *huge* - gigabyte size? – Chloe May 17 '16 at 23:08
  • @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. – Erwin Brandstetter May 18 '16 at 00:03
3

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

bobpoekert
  • 934
  • 1
  • 11
  • 26