Re: Ragged CSV import

On Fri, Sep 11, 2009 at 10:27:06AM +0200, Dimitri Fontaine wrote:
> Maybe instead of opening FROM for COPY, having it accepted in WITH would
> be better, the same way (from the user point of view) that DML returning
> are worked on.
... 
>  WITH csv AS (
>    COPY t FROM stdin CSV
>  )
>  INSERT INTO foo(x, y, z) 
>  SELECT t[3], t[2], mytimestamptz([5], [6], [7])
>    FROM csv;
> 
> Now the text[] has a strange feeling, without it it'd be:
> 
>  WITH csv AS (
>    COPY t(a, b, c, d, e, f, g) 
>    FROM stdin 
>     CSV IGNORING EXTRA COLUMNS         -- random nice syntax
>         MISSING COLUMNS DEFAULTS NULL  -- that needs some reality check
>  )
>  INSERT INTO foo(x, y, z) 
>  SELECT c, b, mytimestamptz(e, f, g)
>    FROM csv;
> 
> The function mytimestamptz(date text, time text, timezone text) will
> accept input that PostgreSQL input types would have errored out on... so
> you can process in one go strange formats from other products.

+1

-dg

-- 
David Gould       daveg@sonic.net      510 536 1443    510 282 0869
If simplicity worked, the world would be overrun with insects.