Re: Conversion of columns during CSV Import

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 (such as a CSV file) as a table inside the database. Once you have that, it should be fairly easy to craft an INSERT ... SELECT that 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.