COPY for CSV documentation

I have reviewed this patch. Basically, CSV is enabled by specifying
more than one delimiter character to COPY, e.g. DELIMITER ',"' or
DELIMITER ',""'. Is this API good for folks?

Prior to 7.2, a multi-character delimiter could be specified, but only
the first character was used. 7.2 release notes state:

* COPY DELIMITERS string must be exactly one character (Tom)

I am a little worried about multibyte too, as you mentioned. It doesn't
look like we support multi-byte delimiters. I see in the code:

if (strlen(delim) != 1)
ereport(ERROR,

With this code, if you use multibyte delimiters, you get an error.
However, with CVS, I can see cases where someone trying to use multibyte
would get a very strange output file rather than an error. It would use
byte one of the multibyte for the delimiter, and the second byte for the
quote, and maybe a third byte for quoting quotes.

We could use pg_mblen(). Maybe we should compute pg_mblen and strlen on
the delimiter and throw an error if the they are different lengths.
That would prevent multibyte delimiters.

Comments?

---------------------------------------------------------------------------

Andrew Dunstan wrote:

Attached is a patch with documentation for the CSV mode of COPY patch
submitted yesterday.

cheers

andrew

Index: doc/src/sgml/ref/copy.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/copy.sgml,v
retrieving revision 1.55
diff -c -r1.55 copy.sgml
*** doc/src/sgml/ref/copy.sgml	13 Dec 2003 23:59:07 -0000	1.55
--- doc/src/sgml/ref/copy.sgml	10 Apr 2004 16:22:23 -0000
***************
*** 136,142 ****
<para>
Specifies copying the OID for each row.  (An error is raised if
<literal>OIDS</literal> is specified for a table that does not
!       have OIDs.)
</para>
</listitem>
</varlistentry>
--- 136,143 ----
<para>
Specifies copying the OID for each row.  (An error is raised if
<literal>OIDS</literal> is specified for a table that does not
!       have OIDs, or if CSV mode is selected by specifying 
!       <literal>DELIMITER</literal> longer then one character.)
</para>
</listitem>
</varlistentry>
***************
*** 145,152 ****
<term><replaceable class="parameter">delimiter</replaceable></term>
<listitem>
<para>
!       The single character that separates columns within each row
!       (line) of the file.  The default is a tab character.
</para>
</listitem>
</varlistentry>
--- 146,162 ----
<term><replaceable class="parameter">delimiter</replaceable></term>
<listitem>
<para>
!       A one- to three-character string containing characters used in importing
!       or exporting Text or Comma Separated Variable (CSV) files.
!       With only one character, Text file format is used.
!       If there are two or three characters, CSV format is used.
!       The first character for either file format is the delimiter character
!       used between values for a single row.
!       The second character is the CSV quote character, 
!       and the third character, if present, is the CSV escape character 
!       used inside quoted values, and defaults to the quote character.
!       The default for this parameter is a single tab character. (Thus TEXT
!       file mode is the default.)
</para>
</listitem>
</varlistentry>
***************
*** 156,163 ****
<listitem>
<para>
The string that represents a null value. The default is
!       <literal>\N</literal> (backslash-N). You might prefer an empty
!       string, for example.
</para>

<note>
--- 166,174 ----
<listitem>
<para>
The string that represents a null value. The default is
!       <literal>\N</literal> (backslash-N), unless a CSV is being processed,
!       in which case it is an empty string. You might prefer an empty string 
!       in any case.
</para>

<note>
***************
*** 168,173 ****
--- 179,197 ----
<command>COPY TO</command>.
</para>
</note>
+ 
+      <note>
+       <para>
+        If you don't want anything used as null when using 
+        <command>COPY FROM</command>, you can specify some value that is very 
+        unlikely to appear in the file, such as <literal>frobnitz</literal> or 
+        <literal>d5f4074b254c76cd8ae37bf1731f4aed</literal> (which is 
+        <literal>md5('frobnitz')</literal>). This could be especially useful 
+        when importing a CSV file into a table with <literal>NOT NULL</>
+        columns.
+       </para>
+      </note>
+ 
</listitem>
</varlistentry>
</variablelist>
***************
*** 252,259 ****
<title>Text Format</title>

<para>
!     When <command>COPY</command> is used without the <literal>BINARY</literal> option,
!     the data read or written is a text file with one line per table row.
Columns in a row are separated by the delimiter character.
The column values themselves are strings generated by the
output function, or acceptable to the input function, of each
--- 276,285 ----
<title>Text Format</title>

<para>
!     When <command>COPY</command> is used without the <literal>BINARY</literal> 
!     option, the data read or written is a text file with one line per table 
!     row unless <literal>DELIMITER</literal> is longer than one character,
!     in which case CSV format is used.
Columns in a row are separated by the delimiter character.
The column values themselves are strings generated by the
output function, or acceptable to the input function, of each
***************
*** 380,385 ****
--- 406,459 ----
</refsect2>

<refsect2>
+    <title>CSV format</title>
+ 
+    <para>
+     This format is used for importing from and exporting to the Comma
+     Separated Variable (CSV) file format used by many other programs,
+     such as spreadsheets. Instead of escaping in 
+     <productname>PostgreSQL</productname>'s standard text mode, it produces
+     and recognises the common CSV escaping mechanism.
+    </para>
+ 
+    <para>
+     The values in each record are separated by the delimiter character, which 
+     is the first character in the <literal>DELIMITER</literal> parameter.
+     If the value contains the delimiter character, the quote character (the 
+     second character in the <literal>DELIMITER</literal> parameter), or a
+     carriage return or line feed character, then the whole value is prefixed
+     and suffixed by the quote character, and any occurrence within the value
+     of a quote character or the escape character (the third character in the
+     <literal>DELIMITER</literal> parameter if present, or else just the quote
+     character itself) is preceded by the escape character.
+    </para>
+ 
+    <para>
+     In the most common cases, simply specifying <literal>DELIMITER ',"'</> 
+     or <literal>DELIMITER ',\''</> should suffice, both for 
+     <command>COPY FROM</> and <command>COPY TO</>.
+    </para>
+ 
+    <note>
+     <para>
+      CSV mode will both recognise and produce CSV files with quoted values
+      containing embedded carriage returns and line feeds. Thus the files are
+      not strictly one line per table row, as TEXT files are.
+     </para>
+    </note>
+ 
+    <note>
+     <para>
+      Many programs produce strange and occasionally perverse CSV files, and
+      the file format is a convention rather than a standard. Thus you might
+      encounter some files that cannot be imported using this mechanism, and
+      you might produce files that other programs fail to recognise properly.
+     </para>
+    </note>
+     
+   </refsect2>
+ 
+   <refsect2>
<title>Binary Format</title>

<para>
***************
*** 532,537 ****
--- 606,620 ----
using the vertical bar (<literal>|</literal>) as the field delimiter:
<programlisting>
COPY country TO STDOUT WITH DELIMITER '|';
+ </programlisting>
+   </para>
+ 
+   <para>
+    To do the same thing but instead produce a standard CSV file, using 
+    <literal>,</> as the delimiter and <literal>"</> as both the quote and 
+    escape characters:
+ <programlisting>
+ COPY country TO STDOUT WITH DELIMITER ',"';
</programlisting>
</para>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

I have reviewed this patch. Basically, CSV is enabled by specifying
more than one delimiter character to COPY, e.g. DELIMITER ',"' or
DELIMITER ',""'. Is this API good for folks?

(if not I'll be displeased, as it is what was the general consensus
about a month ago)

Prior to 7.2, a multi-character delimiter could be specified, but only
the first character was used. 7.2 release notes state:

* COPY DELIMITERS string must be exactly one character (Tom)

I am a little worried about multibyte too, as you mentioned.

I am not sure that CSVs even make sense in a multibyte world. If not, it
would make sense to disable it in such a case.

(Is that your only worry?)

cheers

andrew

Andrew Dunstan wrote:

Bruce Momjian wrote:

I have reviewed this patch. Basically, CSV is enabled by specifying
more than one delimiter character to COPY, e.g. DELIMITER ',"' or
DELIMITER ',""'. Is this API good for folks?

(if not I'll be displeased, as it is what was the general consensus
about a month ago)

OK

Prior to 7.2, a multi-character delimiter could be specified, but only
the first character was used. 7.2 release notes state:

* COPY DELIMITERS string must be exactly one character (Tom)

I am a little worried about multibyte too, as you mentioned.

I am not sure that CSVs even make sense in a multibyte world. If not, it
would make sense to disable it in such a case.

(Is that your only worry?)

Yes, my worry is that someone will use a multibyte character that the
system sees as several bytes and enters CSV mode.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

Prior to 7.2, a multi-character delimiter could be specified, but only
the first character was used. 7.2 release notes state:

* COPY DELIMITERS string must be exactly one character (Tom)

I am a little worried about multibyte too, as you mentioned.

I am not sure that CSVs even make sense in a multibyte world. If not, it
would make sense to disable it in such a case.

(Is that your only worry?)

Yes, my worry is that someone will use a multibyte character that the
system sees as several bytes and enters CSV mode.

How about if we specify it explicitly, like BINARY, instead of it being
implied by the length of DELIMITER?

COPY a FROM stdin CSV DELIMITER ',"';

That would make the patch somewhat more extensive, but maybe not hugely
more invasive (I tried to keep it as uninvasive as possible). I could do
that, I think.

cheers

andrew

Andrew Dunstan wrote:

I am not sure that CSVs even make sense in a multibyte world. If not, it
would make sense to disable it in such a case.

(Is that your only worry?)

Yes, my worry is that someone will use a multibyte character that the
system sees as several bytes and enters CSV mode.

How about if we specify it explicitly, like BINARY, instead of it being
implied by the length of DELIMITER?

COPY a FROM stdin CSV DELIMITER ',"';

That would make the patch somewhat more extensive, but maybe not hugely
more invasive (I tried to keep it as uninvasive as possible). I could do
that, I think.

That's what I was wondering. Is triggering CSV for multi-character
delimiters a little too clever? This reminds me of the use of LIMIT X,Y
with no indication which is limit and which is offset.

We certainly could code to prevent the multibyte problem I mentioned,
but should we?

I am thinking just:

COPY a FROM stdin WITH CSV ',"';

or

COPY a FROM stdin WITH DELIMITER "," QUOTE '"' EQUOTE '"';

EQUOTE for embedded quote. These are used in very limited situations
and don't have to be reserved words or anything.

I can help with these changes if folks like them.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian said:

Yes, my worry is that someone will use a multibyte character that the
system sees as several bytes and enters CSV mode.

How about if we specify it explicitly, like BINARY, instead of it
being implied by the length of DELIMITER?

COPY a FROM stdin CSV DELIMITER ',"';

That would make the patch somewhat more extensive, but maybe not
hugely more invasive (I tried to keep it as uninvasive as possible).
I could do that, I think.

That's what I was wondering. Is triggering CSV for multi-character
delimiters a little too clever? This reminds me of the use of LIMIT
X,Y with no indication which is limit and which is offset.

We certainly could code to prevent the multibyte problem I mentioned,
but should we?

I confess that in my anglocentric world I have remained lamentably
ignorant of how MBCS works. Just reading up a little, and looking over
some of our code (e.g. the scanner) it looks like the simple solution
would be to check that the delimiter was 8-bit clean. (I assume that ASCII
is a subset of every MBCS we support - is that correct?)

However ...

I am thinking just:

COPY a FROM stdin WITH CSV ',"';

or

COPY a FROM stdin WITH DELIMITER "," QUOTE '"' EQUOTE '"';

EQUOTE for embedded quote. These are used in very limited situations
and don't have to be reserved words or anything.

I can help with these changes if folks like them.

I prefer either the first, because it ensures things are specified
together.

If you want to do that I will work on some regression tests.

cheers

andrew

Bruce Momjian <pgman@candle.pha.pa.us> writes:

That's what I was wondering. Is triggering CSV for multi-character
delimiters a little too clever? This reminds me of the use of LIMIT X,Y
with no indication which is limit and which is offset.

I agree, this seems risky and not at all readable to someone who doesn't
remember exactly how the parameter is defined.

COPY a FROM stdin WITH DELIMITER "," QUOTE '"' EQUOTE '"';

EQUOTE for embedded quote.

ESCAPE would be better no? It's already a keyword ...

BTW, don't forget that the syntax options have to be provided in psql's
\copy as well. Did the patch cover that?

regards, tom lane

Andrew Dunstan wrote:

I am thinking just:

COPY a FROM stdin WITH CSV ',"';

or

COPY a FROM stdin WITH DELIMITER "," QUOTE '"' EQUOTE '"';

EQUOTE for embedded quote. These are used in very limited situations
and don't have to be reserved words or anything.

I can help with these changes if folks like them.

I prefer either the first, because it ensures things are specified
together.

If you want to do that I will work on some regression tests.

( Jump to the bottom for my final solution.)

I thought about this today. I am thinking of:

COPY a FROM stdin WITH CSV

or

COPY a FROM stdin WITH CSV '""' DELIMITER ','

In other words, the string after CSV is optional. However, looking at
the COPY syntax, there isn't any case where we have an optional string
after a keyword. Is that OK?

In this case, CVS is a mode that makes the delimiter ',' and the quote
and quote escape '"'. This way, CVS doesn't require any special quote
if you want the default.

However, this still has CSV using a two-character string with special
meaning for the first and second characters. What if we call it QUOTE
mode:

COPY a FROM stdin WITH QUOTE

that enables CVS with comma delimiters and '"' for quote escape, so the
above would be equavalent to:

COPY a FROM stdin WITH QUOTE '"' ESCAPE '"' DELIMITER ','

(I have used ESCAPE because Tom suggested it and it is already a
keyword.)

I am a little worried that ESCAPE only has meaning with QUOTE, and QUOTE
sets defaults for all the others. This makes the syntax addition pretty
confusing for users.

---------------------------------------------------------------------------

Thinking further, maybe we need to add CSV, QUOTE, and ESCAPE to COPY.
QUOTE and ESCAPE are only available in CVS mode, so you can say:

COPY a FROM stdin WITH CSV

or

COPY a FROM stdin WITH CSV ESCAPE '\\'

This means that there is no optional string for keywords. Here is the
line at the bottom we have to add to the COPY syntax.

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]

[ CSV [ QUOTE 'quote' ] [ ESCAPE 'escape' ] ]

DELIMITER default to tab, except in CSV mode, where it is a comma.

That sounds very clear to me.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

That's what I was wondering. Is triggering CSV for multi-character
delimiters a little too clever? This reminds me of the use of LIMIT X,Y
with no indication which is limit and which is offset.

I agree, this seems risky and not at all readable to someone who doesn't
remember exactly how the parameter is defined.

Yep.

COPY a FROM stdin WITH DELIMITER "," QUOTE '"' EQUOTE '"';

EQUOTE for embedded quote.

ESCAPE would be better no? It's already a keyword ...

Yep.

BTW, don't forget that the syntax options have to be provided in psql's
\copy as well. Did the patch cover that?

Oh, yea. That needs to be done too.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

In other words, the string after CSV is optional. However, looking at
the COPY syntax, there isn't any case where we have an optional string
after a keyword. Is that OK?

Seems better to avoid it.

However, this still has CSV using a two-character string with special
meaning for the first and second characters.

One point that I don't think was made before is that if we do any such
thing, we'll be forever foreclosing any chance of allowing
multi-character delimiters. ISTM that would not be forward-looking.

regards, tom lane

Tom Lane said:

COPY a FROM stdin WITH DELIMITER "," QUOTE '"' EQUOTE '"';

EQUOTE for embedded quote.

ESCAPE would be better no? It's already a keyword ...

much better.

BTW, don't forget that the syntax options have to be provided in psql's
\copy as well. Did the patch cover that?

No, because it didn't make a syntax change. I admit I didn't test that
though.

cheers

andrew

Bruce Momjian said:

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]

[ CSV [ QUOTE 'quote' ] [ ESCAPE 'escape' ] ]

DELIMITER default to tab, except in CSV mode, where it is a comma.

That sounds very clear to me.

TIMTOWTDI, as we say in the perl world, and we could debate it endlessly.
This looks fine to me.

a few points:

. ESCAPE should default to same-as-quote
. in CSV mode, NULL should default to '' - that was in what I sent in.
. I assume that for now we will enforce the one byte rule for QUOTE and
ESCAPE as well as for DELIMITER.

cheers

andrew

Andrew Dunstan wrote:

Bruce Momjian said:

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]

[ CSV [ QUOTE 'quote' ] [ ESCAPE 'escape' ] ]

DELIMITER default to tab, except in CSV mode, where it is a comma.

That sounds very clear to me.

TIMTOWTDI, as we say in the perl world, and we could debate it endlessly.
This looks fine to me.

a few points:

. ESCAPE should default to same-as-quote
. in CSV mode, NULL should default to '' - that was in what I sent in.
. I assume that for now we will enforce the one byte rule for QUOTE and
ESCAPE as well as for DELIMITER.

Yes, agreed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian said:

Andrew Dunstan wrote:

Bruce Momjian said:

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]

[ CSV [ QUOTE 'quote' ] [ ESCAPE 'escape' ] ]

DELIMITER default to tab, except in CSV mode, where it is a comma.

That sounds very clear to me.

TIMTOWTDI, as we say in the perl world, and we could debate it
endlessly. This looks fine to me.

a few points:

. ESCAPE should default to same-as-quote
. in CSV mode, NULL should default to '' - that was in what I sent in.
. I assume that for now we will enforce the one byte rule for QUOTE
and ESCAPE as well as for DELIMITER.

Andrew, would you like me to take your patch, implement the changes
discussed, add \copy, and post the result for your testing?

That would be great. Thanks. I don't think the changes to what I did with
copy.c will be huge.

BTW, how to do this is asked at least once a day on the #postgresql
channel on freenode - a lot of people *really* want this.

cheers

andrew

On Mon, Apr 12, 2004 at 02:26:14 -0400,
Andrew Dunstan <andrew@dunslane.net> wrote:

a few points:

. in CSV mode, NULL should default to '' - that was in what I sent in.

Postgres normally treats an empty string as an empty string. Are you sure
you really want it to be treated as a NULL by default in this one place?

Bruno Wolff III said:

On Mon, Apr 12, 2004 at 02:26:14 -0400,
Andrew Dunstan <andrew@dunslane.net> wrote:

a few points:

. in CSV mode, NULL should default to '' - that was in what I sent in.

Postgres normally treats an empty string as an empty string. Are you
sure you really want it to be treated as a NULL by default in this one
place?

Yes ;-)

Otherwise, what will happen when we try to import into some non-text field
for which '' is not a valid value?

Spreadsheets commonly represent missing values as empty strings when
reading/writing CSVs - that's why this behaviour should be the default.

If you want to force it to use an empty string instead, simply specify
some unlikely value for NULL, like 'frobnitz'. But if you do, be prepared
for lots of errors unless you are importing into fields where empty string
is a valid text value.

Of course, a NOT NULL constraint will also break things, unless we went to
the MySQL method of handling insertion of NULL into a NOT NULL field ...
no I really am kidding.

cheers

andrew

On Mon, Apr 12, 2004 at 08:07:12 -0400,
Andrew Dunstan <andrew@dunslane.net> wrote:

Otherwise, what will happen when we try to import into some non-text field
for which '' is not a valid value?

I would expect the copy to fail as it does normally.

Spreadsheets commonly represent missing values as empty strings when
reading/writing CSVs - that's why this behaviour should be the default.

That doesn't strike me as an overwhelming reason to do it that way.

If you want to force it to use an empty string instead, simply specify
some unlikely value for NULL, like 'frobnitz'. But if you do, be prepared
for lots of errors unless you are importing into fields where empty string
is a valid text value.

This concerns me. If NULL is on by default, there should be some way to
turn it off, not to change it to some value you hope won't show up in
the input stream.

Bruno Wolff III <bruno@wolff.to> writes:

On Mon, Apr 12, 2004 at 02:26:14 -0400,
Andrew Dunstan <andrew@dunslane.net> wrote:

a few points:
. in CSV mode, NULL should default to '' - that was in what I sent in.

Postgres normally treats an empty string as an empty string. Are you sure
you really want it to be treated as a NULL by default in this one place?

I think that's a spectacularly bad idea too. People who really want
that can write "NULL ''", but it shouldn't be implied by CSV mode.

regards, tom lane

Tom Lane wrote:

Bruno Wolff III <bruno@wolff.to> writes:

On Mon, Apr 12, 2004 at 02:26:14 -0400,
Andrew Dunstan <andrew@dunslane.net> wrote:

a few points:
. in CSV mode, NULL should default to '' - that was in what I sent in.

Postgres normally treats an empty string as an empty string. Are you sure
you really want it to be treated as a NULL by default in this one place?

I think that's a spectacularly bad idea too. People who really want
that can write "NULL ''", but it shouldn't be implied by CSV mode.

Spectacularly? Hmm.

My approach was that the default should be the most common case. Perhaps
on import it's a tossup, but on export a CSV containing lots of \N cells
is likely to be ... unexpected.

But, honestly, it's not worth dying in a ditch over.

cheers

andrew