Subject: COPY manual is ambiguous about column list

From: PG Doc comments form <[email-masked]>
Date: Wed, 27 Nov 2019 17:54:53 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/sql-copy.html
Description:

"If a list of columns is specified, COPY will only copy the data in the
specified columns to or from the file. If there are any columns in the table
that are not in the column list, COPY FROM will insert the default values
for those columns."

I clearly see "to or from" file here. But if one tries to COPY FROM filename
with more columns than list specified, the process fails:

```
pasha@PG480 MINGW64 ~
$ echo "col1,col2,col3" > test.csv

pasha@PG480 MINGW64 ~
$ cat test.csv
col1,col2,col3

pasha@PG480 MINGW64 ~
$ psql -d migrator

migrator=# CREATE TABLE copy_test(col1 text, col2 text);
CREATE TABLE

migrator=# COPY copy_test(col1, col2) FROM 'test.csv' (FORMAT csv);
ERROR:  extra data after last expected column
CONTEXT:  COPY copy_test, line 1: "col1,col2,col3"
```

I believe this statement should be rewritten, e.g.
"If a list of columns is specified, COPY will only copy the data in the
specified columns to the file. The input file should contain the same number
of columns as the list specified, otherwise COPY FROM will fail. One may use
`awk` to preprocess the input file and remove extra columns. If there are
any columns in the table that are not in the column list, COPY FROM will
insert the default values for those columns."

https://www.postgresql.org/docs/12/sql-copy.html

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 28 Nov 2019 10:47:10 +0100

> with more columns than list specified, the process fails:

I think you misunderstood the documentation.

Nowhere in that sentence is the documentation talking about columns in
the file, only columns in the table.

But if you got it wrong, maybe a clarification would be a good idea.

Yours,
Laurenz Albe

---

From: Pavlo Golub <[email-masked]>
Date: Thu, 28 Nov 2019 14:53:03 +0100

> I think you misunderstood the documentation.

Yeap, I did. Even two of us. Laetitia was helping me :)

> But if you got it wrong, maybe a clarification would be a good idea.

I think it better to have more details to avoid confusion.

-- 
--
Hoc est vivere bis, vita posse priore frui.
Nullus est in vitae sensus, ipsa vera est sensus.

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 19 Dec 2019 11:53:40 -0500

> I think it better to have more details to avoid confusion.

How is the attached patch?

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
new file mode 100644
index d9b7c4d..475d40c
*** a/doc/src/sgml/ref/copy.sgml
--- b/doc/src/sgml/ref/copy.sgml
*************** COPY { <replaceable class="parameter">ta
*** 63,71 ****
    <para>
     If a list of columns is specified, <command>COPY</command> will
     only copy the data in the specified columns to or from the file.
!    If there are any columns in the table that are not in the column list,
!    <command>COPY FROM</command> will insert the default values for
!    those columns.
    </para>
  
    <para>
--- 63,72 ----
    <para>
     If a list of columns is specified, <command>COPY</command> will
     only copy the data in the specified columns to or from the file.
!    In <command>COPY FROM</command>, the number of columns specified
!    must match the number of fields in the file;  any table columns not
!    specified in the <command>COPY FROM</command> column list will insert
!    their default values.
    </para>
  
    <para>

---

From: Pavlo Golub <[email-masked]>
Date: Thu, 19 Dec 2019 17:56:38 +0100

> How is the attached patch?

Sounds great to me! Thanks!

---

From: Tom Lane <[email-masked]>
Date: Thu, 19 Dec 2019 23:46:37 -0500

Phrasing seems a bit weird to me: you've run two nearly independent
observations into one sentence.  But s/; any/. Any/ might be enough
to fix it.

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 19 Dec 2019 22:07:48 -0700

> observations into one sentence.  But s/; any/. Any/ might be enough

Seems better to simply talk about each form independently:

If a list of columns is specified, <command>COPY TO</command> will only
copy the data in the specified columns to the file.  <command>COPY
FROM</command> will, by position (i.e., the number of columns listed must
match the number of columns in the file), match each file column to the
named column in the table.  Any additional columns present in the table
will receive their default value.

David J.

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Dec 2019 00:32:30 -0500

> will receive their default value.

+1 for separating it like that, but your middle sentence reads awkwardly
to me.  How about something like

  <command>COPY FROM</command> will match each listed column in the table
  to a file column by position (so the number of columns listed must match
  the number of columns in the file).

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 19 Dec 2019 22:33:48 -0700

>   the number of columns in the file).

+1

David J.

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 20 Dec 2019 08:45:41 -0500

>       the number of columns in the file).

OK, how is this?

index d9b7c4d..cf19706
*** 61,71 ****
    </para>
  
    <para>
!    If a list of columns is specified, <command>COPY</command> will
!    only copy the data in the specified columns to or from the file.
!    If there are any columns in the table that are not in the column list,
!    <command>COPY FROM</command> will insert the default values for
!    those columns.
    </para>
  
    <para>
--- 61,72 ----
    </para>
  
    <para>
!    If a column list is specified, <command>COPY TO</command> copies only
!    the data in the specified columns to the file.  If a column list is
!    specified in <command>COPY FROM</command>, the number and position of
!    the columns must match those fields in the file.  Table columns not
!    specified in the <command>COPY FROM</command> column list will insert
!    their default values.
    </para>
  
    <para>

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Dec 2019 09:13:33 -0500

> OK, how is this?

I do not like the phrasing of

    If a column list is
    specified in <command>COPY FROM</command>, the number and position of
    the columns must match those fields in the file.

I'm not quite sure why, but that reads to me as implying that the COPY
will identify columns of the file by name and match them up.  Or at
least it could be read that way by somebody who was predisposed to
believe it.  I'd go with something more like

    If a column list is
    specified in <command>COPY FROM</command>, only the listed columns
    are read from the file (whose fields must match the column list).

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 20 Dec 2019 07:29:33 -0700

>     are read from the file (whose fields must match the column list).

This has the same "match by name" possibility when I read it...

I don't see the benefit of repeating "If a column list is specified" a
second time in the three sentence paragraph.  To a lesser extent the same
goes for the second repetition of COPY FROM.

I agree with the general goal of trying to get rid of the parenthetical; to
that end:
<command>COPY FROM</command> will match all listed columns in the table to
*all* file columns by position.

That sufficiently implies the "must have equal numbers" and, frankly, if
someone gets that part wrong the immediate failure combined with re-reading
the word "all" should provide sufficient clarity.

So I'm still for Tom and mine combined proposal with cleaning up the middle
sentence even more.

The other point of note is the difference between:

will receive their default value
and
will insert the default values

The system is inserting default values but the columns are receiving them.
The sentence is relative to the table columns though so "receive" seems
like the better fit.  Minor point overall though.

David J.

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 20 Dec 2019 10:01:28 -0500

On Fri, Dec 20, 2019 at 07:29:33AM -0700, David G. Johnston wrote:
> I don't see the benefit of repeating "If a column list is specified" a second
> time in the three sentence paragraph.  To a lesser extent the same goes for the
> second repetition of COPY FROM.
> 
> someone gets that part wrong the immediate failure combined with re-reading the
> word "all" should provide sufficient clarity.
> 
> The system is inserting default values but the columns are receiving them.  The
> sentence is relative to the table columns though so "receive" seems like the
> better fit.  Minor point overall though.

OK, this wording is obviously harder than I thought.  Updated patch
attached.

index d9b7c4d..a99f815
  
    <para>
  
    <para>
--- 61,71 ----
    </para>
  
    <para>
!    If a column list is specified, <command>COPY TO</command> copies only
!    the data in the specified columns to the file.  For <command>COPY
!    FROM</command>, each field in the file is inserted, in order, into the
!    specified column.  Table columns not specified in the <command>COPY
!    FROM</command> column list will receive their default values.
    </para>
  
    <para>

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Dec 2019 11:21:20 -0500

> OK, this wording is obviously harder than I thought.  Updated patch

That one works for me.

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 20 Dec 2019 09:45:35 -0700

> > OK, this wording is obviously harder than I thought.  Updated patch

Me too.

David J.

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 21 Dec 2019 12:45:07 -0500

>     > OK, this wording is obviously harder than I thought.  Updated patch

Patch applied to all supported releases.  Thanks for the feedback.