This works fine...
COPY pgbench_accounts TO '/tmp/acc' BINARY;
This new format does not
COPY pgbench_accounts FROM '/tmp/acc' (FORMAT BINARY);
ERROR: syntax error at or near "BINARY" at character 47
which looks like I've mistyped something. Until you realise that this
statement gives a completely different error message.
COPY pgbench_accounts FROM '/tmp/acc' (FORMAT anyname);
ERROR: COPY format "anyname" not recognized
and we also note that there are no examples in the docs, nor
regression tests to cover this situation.
So I conclude that this hasn't ever worked since it was introduced in 9.0.
The cause is that there is an overlap between the old and the new COPY
syntax, relating to the word BINARY. It's the grammar generating the
error, not post parse analysis.
My attempts to fix that look pretty ugly, so I'm not even going to
post them. I can stop the error on binary by causing errors on csv and
text, obviously not a fix. Any grammar based fix looks like it would
restrict the list of formats, which breaks the orginal intention of
the syntax change.
I'm advised that
COPY pgbench_accounts FROM '/tmp/acc' (FORMAT 'BINARY');
works fine. Though that is not documented and I doubt anyone much uses that.
My conclusion is that we should *not* fix this bug, but just alter the
manual slightly to explain what the correct usage is (use quotes
around 'binary'). Reason for that suggestion is that nobody ever
reported this bug, so either few people use binary mode or they use
the old syntax. Of course, that is not a normal suggestion, so feel
free to walk up and down my spine with boots on for suggesting it.
Thoughts?
--Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
