Subject: COPY options

From: "Andrei M. Eichler" <[email-masked]>
Date: Thu, 28 Apr 2016 13:58:22 -0300

Hi everyone,

In the documentation of COPY options, it lists the following options with a
"_" in it's name:

FORCE_QUOTE
FORCE_NOT_NULL
FORCE_NULL

But 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"

Shouldn't it be without the "_" in the docs?

---

From: Kevin Grittner <[email-masked]>
Date: Thu, 28 Apr 2016 15:27:22 -0500

On Thu, Apr 28, 2016 at 11:58 AM, Andrei M. Eichler
<[email-masked]> wrote:

> when one tries to use it as it is from the docs, one gets the following
> results:

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

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: "Andrei M. Eichler" <[email-masked]>
Date: Fri, 29 Apr 2016 12:31:37 -0300

Hi, thanks for the wiki link.

I'm using Postgrees 9.5.2, running on Debian 8.4

I have a csv file with all columns quoted, even null values.

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';

And received this error message:

ERROR:  syntax error at or near "force_null"
LINE 1: ...test_file.csv' with csv header delimiter ';' force_null...

To fix the error, I had to replace the "_" to " ", so "force_null" becomes
"force null" :

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';

COPY 99999

In the docs (http://www.postgresql.org/docs/current/static/sql-copy.html)
the "FORCE" options are written with "_" but the correct name is with " "

---

From: Tom Lane <[email-masked]>
Date: Fri, 29 Apr 2016 11:50:38 -0400

> 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

---

From: Kevin Grittner <[email-masked]>
Date: Fri, 29 Apr 2016 10:53:57 -0500

> 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.

---

From: Tom Lane <[email-masked]>
Date: Fri, 29 Apr 2016 12:03:44 -0400

> 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

---

From: "Andrei M. Eichler" <[email-masked]>
Date: Fri, 29 Apr 2016 13:30:07 -0300

Thanks for the replies, I'm using postgres since 8.3 and didn't notice at
the time of 9.0 release the change in the syntax, as the old syntax still
works, every time I read the current documentation I didn't notice that I
was using it wrong.

I agree with Tom Lane about leaving it alone.

Another question, I don't know if I should ask it here, why FORCE_NOT_NULL
and FORCE_NULL doesn't allow * as list of columns as FORCE_QUOTE?