Re: COPY formatting

Andrew Dunstan wrote:
> Lee Kindness wrote:
>
> >To be honest this idea strikes me as overkill - over
> >engineering. While there is a clear need for proper CSV import
> >(i.e. just setting DELIMITER to ',' doesn't work due to ','s in
> >strings) I cannot see how this would prove useful, or who would use
> >it?
> >
> >
> >
> I agree. My modest proposal for handling CSVs would be to extend the
> DELIMITER parameter to allow up to 3 characters - separator, quote and
> escape. Escape would default to the quote char and the quote char would
> default to unspecified. This would involve no grammar changes and fairly
> isolated and small code changes, I believe. In the most common CSV cases
> you would just use $$,"$$ or $$,'$$. :-)
>
> COPY is basically line/tuple oriented, and that alone would exclude many
> file formats (e.g. imagine wanting to import a spreadsheet where each
> worksheet was the table name and the first row on each worksheet was the
> field names - I have seen such beasts more than once). If we want a
> general facility for loading and exporting foreign file formats, I
> really believe that is the province of a utility program rather than a
> database engine function.
>
> The reason in my mind for making CSV a special case is that it is very
> easy to do and so often asked for.
>
> (I used to set parsing CSVs as a basic programming exercise - it is
> amazing how many way people find to get it wrong).

I like the separator, quote, and escape idea. It allows variety without
requiring folks to code in C.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073