CODEWITHSUNDEEP
postgresqlsqlbulkcopy
Dinesh
Dinesh

Reputation: 4559

postgresql how to have COPY interpret formatted numeric fields automatically?

I have an input CSV file containing something like:

SD-32MM-1001,"100.00",4/11/2012
SD-32MM-1001,"1,000.00",4/12/2012

I was trying to COPY import that into a postgresql table(varchar,float8,date) and ran into an error:

# copy foo from '/tmp/foo.csv' with header csv;
ERROR:  invalid input syntax for type double precision: "1,000.00"
Time: 1.251 ms

Aside from preprocessing the input file, is there some setting in PG that will have it read a file like the one above and convert to numeric form in COPY? Something other than COPY?

If preprocessing is required, can it be set as part of the COPY command? (Not the psql \copy)?

Thanks a lot.

Upvotes: 2

Views: 2004

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95722

It's an odd CSV file that surrounds numeric values with double quotes, but leaves values like SD-32MM-1001 unquoted. In fact, I'm not sure I've ever seen a CSV file like that.

If I were in your shoes, I'd try copy against a file formatted like this.

"SD-32MM-1001",100.00,4/11/2012
"SD-32MM-1001",1000.00,4/12/2012

Note that numbers have no commas. I was able to import that file successfully with

copy test from '/fullpath/test.dat' with csv

I think your best bet is to get better formatted output from your source.

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

The option to pre processing is to first copy to a temporary table as text. From there insert into the definitive table using the to_number function:

select to_number('1,000.00', 'FM000,009.99')::double precision;

Upvotes: 3

Related Questions