Re: COPY enhancements

Hi Robert,

> I like this idea, perhaps not surprisingly (for those not following at
> home: that was my patch). Unfortunately, it looks to me like there is
> no way to do this without overhauling the syntax. If the existing
> syntax required a comma between options (i.e. "copy blah to stdout
> binary, oids" rather than "copy to stdout binary oids", this would be
> pretty straightforward; but it doesn't even allow one).
>
Well some options like CSV FORCE ... take a comma separated list of
columns. This would require all options to become reserved keywords or
force parenthesis around option parameters.
> I wonder if we should consider allowing COPY options to be
> comma-separated beginning with 8.5, and then require it in 8.6. Other
> options include continuing to support the old syntax for the existing
> options, but allowing some new syntax as well and requiring its use
> for all new options (this is what we did with EXPLAIN, but there were
> only two pre-existing options there), and just changing the syntax
> incompatibly and telling users to suck it up. But I'm not sure I like
> either of those choices.
>
We could keep the current syntax for backward compatibility only (can be
dropped in a future release) and have a new syntax (starting in 8.5). To
avoid confusion between both, we could just replace WITH with something
else (or just drop it) to indicate that this is the new syntax.

The new syntax could look like:

COPY /tablename/ [ ( /column/ [, ...] ) ]
FROM { '/filename/' | STDIN }
[ [, BINARY ]
[, OIDS ]
[, DELIMITER [ AS ] '/delimiter/' ]
[, NULL [ AS ] '/null string/' ]
[, CSV [ HEADER ]
[ QUOTE [ AS ] '/quote/' ]
[ ESCAPE [ AS ] '/escape/' ]
[ FORCE NOT NULL (/column/ [, ...]) ]
[, ERRORS { SKIP |
LOG INTO { tablename | 'filename' }
[ LABEL label_name ]
[ KEY key_name ]
[ MAX ERRORS /count/ ] } ]

Is this what you had in mind?

Emmanuel

--
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com