user3732694
user3732694

Reputation: 175

COPY only some columns from an input CSV?

I have created a table in my database with name 'con' which has two columns with the name 'date' and 'kgs'. I am trying to extract data from this 'hi.rpt' file copied on this location 'H:Sir\data\reporting\hi.rpt' and want to store values in the table 'con' in my database.

I have tried this code in pgadmin

When I run:

COPY con (date,kgs) 
FROM 'H:Sir\data\reporting\hi.rpt'
WITH DELIMITER ','
CSV HEADER 
    date AS 'Datum/Uhrzeit'
    kgs  AS 'Summe'

I get the error:

ERROR:  syntax error at or near "date"
LINE 5:    date AS 'Datum/Uhrzeit' 
           ^
********** Error **********
ERROR: syntax error at or near "date"
SQL state: 42601
Character: 113

"hi.rpt" file from which i am reading the data look like this:

Datum/Uhrzeit,Sta.,Bez.,Unit,TBId,Batch,OrderNr,Mat1,Total1,Mat2,Total2,Mat3,Total3,Mat4,Total4,Mat5,Total5,Mat6,Total6,Summe
41521.512369(04.09.13 12:17:48),TB01,TB01,005,300,9553,,2,27010.47,0,0.00,0,0.00,3,1749.19,0,0.00,0,0.00,28759.66
41521.547592(04.09.13 13:08:31),TB01,TB01,005,300,9570,,2,27057.32,0,0.00,0,0.00,3,1753.34,0,0.00,0,0.00,28810.66

Is it possible to extract only two data values from 20 different type of data that i have in this 'hi.rpt' file or not?

or is there only a mistake in the syntax that i have written? What is the correct way to write it?

Upvotes: 8

Views: 7381

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324771

I don't know where you got that syntax, but COPY doesn't take a list of column aliases like that. See the help:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

(AS isn't one of the listed options; to see the full output run \d copy in psql, or look at the manual for the copy command online).

There is no mapping facility in COPY that lets you read only some columns of the input CSV. It'd be really useful, but nobody's had the time/interest/funding to implement it yet. It's really only one of many data transform/filtering tasks people want anyway.

PostgreSQL expects the column-list given in COPY to be in the same order, left-to-right, as what's in the CSV file, and have the same number of entries as the CSV file has columns. So if you write:

COPY con (date,kgs)

then PostgreSQL will expect an input CSV with exactly two columns. It'll use the first csv column for the "date" table column and the second csv column for the "kgs" table column. It doesn't care what the CSV headers are, they're ignored if you specify WITH (FORMAT CSV, HEADER ON), or treated as normal data rows if you don't specify HEADER.

PostgreSQL 9.4 adds FROM PROGRAM to COPY, so you could run a shell command to read the file and filter it. A simple Python or Perl script would do the job.

If it's a small file, just open a copy in the spreadsheet of your choice as a csv file, delete the unwanted columns, and save it, so only the date and kgs columns remain.

Alternately, COPY to a staging table that has all the same columns as the CSV, then do an INSERT INTO ... SELECT to transfer just the wanted data into the real target table.

Upvotes: 8

Related Questions