On 29 Jun 2012, at 9:54, Patrick Schneider wrote:
> Hello,
>
> is there any possibility to convert special columns during an CSV import via COPY?
> For example:
>
> HELLO;WORLD;9999;011001
>
> 9999 should be converted to a character field
> 011001 to the date 10th January 2001
>
> For the moment the only idea we have is, to import the CSV into a TEMP table and
> perform the conversion by a select from the temp to the target table.
>
> Thanks for any idea.
I haven't worked with them yet, but as I understand it FDW's (foreign data wrappers) present data from foreign sources
(suchas a CSV file) as a table inside the database. Once you have that, it should be fairly easy to craft an INSERT ...
SELECTthat converts the data on the fly.
Or use an ETL tool - Pentaho is popular.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.