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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073