Обсуждение: COPY options

On Thu, Apr 28, 2016 at 11:58 AM, Andrei M. Eichler
<andrei.eichler@unicheck.com.br> wrote:

> when one tries to use it as it is from the docs, one gets the following
> results:
> ERROR:  syntax error at or near "force_null"

Works for me.

Please copy/paste the actual command and the actual, complete error message.

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

"Andrei M. Eichler" <andrei.eichler@unicheck.com.br> writes:
> Following the documentation, I wrote this COPY FROM command:

> COPY test_copy FROM '/mnt/disk1/files/test_file.csv' with csv header
> delimiter ';' force_null date_column, date_column2, date_column3,
> date_column4, date_column5 encoding 'latin1';

Which part of the documentation, exactly, inspired you to write that?
Certainly not the main syntax summary for COPY, which would tell you
to write something like
    with (format csv, header, ...);

What you've got there is some variant of the pre-9.0 COPY syntax, which is
documented at the bottom of the COPY reference page, and hopefully not
used anywhere else.  FORCE_NULL isn't mentioned in that syntax, and
for that matter neither is FORCE NULL; if that works, it's accidental.

            regards, tom lane

On Fri, Apr 29, 2016 at 10:31 AM, Andrei M. Eichler
<andrei.eichler@unicheck.com.br> wrote:

> Following the documentation, I wrote this COPY FROM command:
>
> COPY test_copy FROM '/mnt/disk1/files/test_file.csv' with csv header
> delimiter ';' force_null date_column, date_column2, date_column3,
> date_column4, date_column5 encoding 'latin1';

The syntax synopsis in the current docs requires parentheses around
any options.  I was a little surprised that you got it to work at
all without them; but then remembered there is older (deprecated
and undocumented) syntax that was not ripped out to avoid breaking
code which was working before the syntax change in 9.0.  To do this
per the current documentation (which I would strongly recommend
over the old, undocumented, deprecated syntax), try this:

COPY test_copy FROM '/mnt/disk1/files/test_file.csv' with (
  format csv,
  header true,
  delimiter ';',
  force_null (date_column, date_column2, date_column3, date_column4,
date_column5),
  encoding 'latin1'
);

This new syntax was adopted to make it easier to add new options,
and to avoid having to define new keywords or reserved words to do
so.

It's not really a question of whether we should go back to
documenting the deprecated syntax, but whether (and when) support
for it should finally be ripped out.  It was last documented in 8.4.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Kevin Grittner <kgrittn@gmail.com> writes:
> It's not really a question of whether we should go back to
> documenting the deprecated syntax, but whether (and when) support
> for it should finally be ripped out.  It was last documented in 8.4.

We still document, and support, not only the pre-9.0 COPY syntax but
also the pre-7.3 COPY syntax; see the bottom of the COPY reference page.
The timescale for getting rid of that kind of thing is *really* long,
in part for fear of breaking old pg_dump backups.

BTW, so far as FORCE NULL goes, I think that feature was implemented
earlier in the 9.0 cycle than where we put in the new generalized
option syntax.  When we did the latter, we reverted the documentation
of the old syntax to exactly match 8.4, but did not take out the
gram.y production allowing the never-released FORCE NULL syntax.
I do not recall if that was intentional or an oversight.
We could (a) document it, (b) remove it, or (c) leave it alone.
At this point I favor the benign neglect approach.

            regards, tom lane