postgresql how to have COPY interpret formatted numeric fields automatically?

postgresql sqlbulkcopy

Question

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.

1
2
4/19/2013 5:21:05 PM

Popular Answer

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;
3
4/19/2013 5:35:42 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow