Thread: COPY syntax improvement
I am working on the TODO item:
o Change syntax to WITH DELIMITER, (keep old syntax around?)
and I have added syntax so COPY can now accept all parameters at the end
using WITH:
COPY table FROM { 'filename' | stdin } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER
'delimiter'] [ NULL AS 'null string' ] ]
(COPY TO is similar.)
For portability, it still supports the old syntax of BINARY after COPY,
WITH OIDS after 'table' and USING DELIMITERS after 'filename'. I have
sent the patch to the patches list.
I have not modified pg_dump so that 7.3 dumps can be loaded into <=7.2
databases. I have modified psql \copy to _only_ use the new syntax, and
to only send the new syntax to the backends. (We don't usually support
new psql in to older databases anyway.) Not sure if I should document
the old syntax somewhere because pg_dump uses WITH OIDS with the old
syntax.
I have not applied the patch. I am waiting for comments.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have not modified pg_dump so that 7.3 dumps can be loaded into <=7.2
> databases. I have modified psql \copy to _only_ use the new syntax, and
> to only send the new syntax to the backends.
Why not leave psql alone? Seems to me you are gratuitously breaking
backwards compatibility of psql, and gaining absolutely zero in return.
I know that a lot of 7.3 psql's other backslash commands will not work
against pre-7.3 servers, but there's no help for that (short of making
psql version-aware like pg_dump is). I don't think that's a reason to
break \copy too, when there is no need for it.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I have not modified pg_dump so that 7.3 dumps can be loaded into <=7.2 > > databases. I have modified psql \copy to _only_ use the new syntax, and > > to only send the new syntax to the backends. > > Why not leave psql alone? Seems to me you are gratuitously breaking > backwards compatibility of psql, and gaining absolutely zero in return. Yes, it was too late last night to think. I have made \copy backward-compatible, and it internally uses the old syntax. > I know that a lot of 7.3 psql's other backslash commands will not work > against pre-7.3 servers, but there's no help for that (short of making > psql version-aware like pg_dump is). I don't think that's a reason to > break \copy too, when there is no need for it. Yep. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote:
> I am working on the TODO item:
>
> o Change syntax to WITH DELIMITER, (keep old syntax around?)
>
> and I have added syntax so COPY can now accept all parameters at the end
> using WITH:
>
> COPY table
> FROM { 'filename' | stdin }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER 'delimiter' ]
> [ NULL AS 'null string' ] ]
>
> (COPY TO is similar.)
Actually, in looking at the grammar, I see no reason NULL should have AS
while DELIMITER does not. New syntax has AS optional for both:
COPY table FROM { 'filename' | stdin } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [
AS] 'delimiter' ] [ NULL [ AS ] 'null string' ] ]
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian writes:
> COPY table
> FROM { 'filename' | stdin }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER 'delimiter' ]
> [ NULL AS 'null string' ] ]
I'm not sure what was wrong with the old syntax except for fixing the
DELIMITER plural. For example, the current
copy mytable with oids from stdin using delimiter '|';
reads very pleasantly, but
copy mytable from stdin with oids delimiter '|';
isn't nearly as good. (E.g., it's not the oids' delimiter, and it's not
*with* delimiter because you don't actually copy the delimiter, you just
use it.)
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > COPY table
> > FROM { 'filename' | stdin }
> > [ [ WITH ]
> > [ BINARY ]
> > [ OIDS ]
> > [ DELIMITER 'delimiter' ]
> > [ NULL AS 'null string' ] ]
>
> I'm not sure what was wrong with the old syntax except for fixing the
> DELIMITER plural. For example, the current
>
> copy mytable with oids from stdin using delimiter '|';
>
> reads very pleasantly, but
>
> copy mytable from stdin with oids delimiter '|';
>
> isn't nearly as good. (E.g., it's not the oids' delimiter, and it's not
> *with* delimiter because you don't actually copy the delimiter, you just
> use it.)
I thought there were complaints that the old COPY syntax just had too
many features stuffed in too many unusual places, e.g. delimiter after
filename, oids after tablename, binary after COPY, NULL after
delimiter. It was just too weird. Now, all the options can be
specified after WITH, like the other SQL commands.
However, the old syntax still works.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > COPY table
> > FROM { 'filename' | stdin }
> > [ [ WITH ]
> > [ BINARY ]
> > [ OIDS ]
> > [ DELIMITER 'delimiter' ]
> > [ NULL AS 'null string' ] ]
>
> I'm not sure what was wrong with the old syntax except for fixing the
> DELIMITER plural. For example, the current
>
> copy mytable with oids from stdin using delimiter '|';
>
> reads very pleasantly, but
>
> copy mytable from stdin with oids delimiter '|';
>
> isn't nearly as good. (E.g., it's not the oids' delimiter, and it's not
> *with* delimiter because you don't actually copy the delimiter, you just
> use it.)
New supported syntax I posted is now DELIMITER [AS] ' '. I noticed that
problem myself, that NULL had AS but DELIMITER didn't.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Well, good points. I know there were some people who wanted a clearer syntax, so I supplied it. Seems you don't. I would like to hear from someone else who doesn't like the improved syntax before I consider changing things back. --------------------------------------------------------------------------- Peter Eisentraut wrote: > Bruce Momjian writes: > > > I thought there were complaints that the old COPY syntax just had too > > many features stuffed in too many unusual places, > > Haven't ever seen one. This command has no precedent in other products, > only years of going virtually unchanged in PostgreSQL. Changing it now > and allowing countless permutations of the key words is going to be > confusing, IMHO. > > > e.g. delimiter after > > filename, > > COPY is the only command to use a delimiter, so this can hardly be > qualified as an "unusual" place. > > > oids after tablename, > > That's because the OIDs are in said table. > > > binary after COPY, > > Which is consistent with DECLARE BINARY CURSOR. > > > NULL after delimiter. > > OK, that order should perhaps be more flexible. > > -- > Peter Eisentraut peter_e@gmx.net > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian writes: > I thought there were complaints that the old COPY syntax just had too > many features stuffed in too many unusual places, Haven't ever seen one. This command has no precedent in other products, only years of going virtually unchanged in PostgreSQL. Changing it now and allowing countless permutations of the key words is going to be confusing, IMHO. > e.g. delimiter after > filename, COPY is the only command to use a delimiter, so this can hardly be qualified as an "unusual" place. > oids after tablename, That's because the OIDs are in said table. > binary after COPY, Which is consistent with DECLARE BINARY CURSOR. > NULL after delimiter. OK, that order should perhaps be more flexible. -- Peter Eisentraut peter_e@gmx.net