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(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.