Re: Conversion of columns during CSV Import

On 06/29/2012 12:54 AM, 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.
Technically everything coming in from the CSV is text and it will be
converted to the data-type in your PostgreSQL table during the import
unless such conversion is not possible in which case it will throw an
error. 9999 will import as text to a text/character field. You could
also import it to an int or numeric provided all data in that column
will convert to the specified type.

Similarly, "011001" will be interpreted per ISO 8601 as 10 January 2001.
But your sample data is ambiguous (month 01 and year 01). If the data
ordering is YMD you are fine. MDY will be problematic and may require a
preprocessing step either inside or outside PostgreSQL.

Given the above data:
create table foo (a text, b text, c text, d date);

An input table (with some data to show where the month and the year
really are:
HELLO;WORLD;9999;011001
Goodbye;World;0000;120629

Copy the data:
\copy foo from foo.txt csv delimiter ';'

Profit:select * from foo;
     a    |   b   |  c   |     d
---------+-------+------+------------
  HELLO   | WORLD | 9999 | 2001-10-01
  Goodbye | World | 0000 | 2012-06-29

Cheers,
Steve