Re: COPY for CSV documentation - Mailing list pgsql-patches

Andrew Dunstan wrote:
> > I am thinking just:
> >
> >> COPY a FROM stdin WITH CSV ',"';
> >
> > or
> >
> >> COPY a FROM stdin WITH DELIMITER "," QUOTE '"' EQUOTE '"';
> >
> > EQUOTE for embedded quote.  These are used in very limited situations
> > and don't have to be reserved words or anything.
> >
> > I can help with these changes if folks like them.
> >
>
>
> I prefer either the first, because it ensures things are specified
> together.
>
> If you want to do that I will work on some regression tests.


( Jump to the bottom for my final solution.)


I thought about this today.  I am thinking of:

> COPY a FROM stdin WITH CSV

or

> COPY a FROM stdin WITH CSV '""' DELIMITER ','

In other words, the string after CSV is optional.  However, looking at
the COPY syntax, there isn't any case where we have an optional string
after a keyword.  Is that OK?

In this case, CVS is a mode that makes the delimiter ',' and the quote
and quote escape '"'.  This way, CVS doesn't require any special quote
if you want the default.

However, this still has CSV using a two-character string with special
meaning for the first and second characters.  What if we call it QUOTE
mode:

> COPY a FROM stdin WITH QUOTE

that enables CVS with comma delimiters and '"' for quote escape, so the
above would be equavalent to:

> COPY a FROM stdin WITH QUOTE '"' ESCAPE '"' DELIMITER ','

(I have used ESCAPE because Tom suggested it and it is already a
keyword.)

I am a little worried that ESCAPE only has meaning with QUOTE, and QUOTE
sets defaults for all the others.  This makes the syntax addition pretty
confusing for users.

---------------------------------------------------------------------------

Thinking further, maybe we need to add CSV, QUOTE, and ESCAPE to COPY.
QUOTE and ESCAPE are only available in CVS mode, so you can say:

> COPY a FROM stdin WITH CSV

or

> COPY a FROM stdin WITH CSV ESCAPE '\\'

This means that there is no optional string for keywords.  Here is the
line at the bottom we have to add to the COPY syntax.

       COPY tablename [ ( column [, ...] ) ]
           TO { 'filename' | STDOUT }
           [ [ WITH ]
                 [ BINARY ]
                 [ OIDS ]
                 [ DELIMITER [ AS ] 'delimiter' ]
                 [ NULL [ AS ] 'null string' ] ]

                 [ CSV [ QUOTE 'quote' ] [ ESCAPE 'escape' ] ]

DELIMITER default to tab, except in CSV mode, where it is a comma.

That sounds very clear to me.

--
  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, Pennsylvania 19073