Here you go.
Emmanuel
Dan Colish wrote:
On Thu, Sep 17, 2009 at 02:56:07PM -0400, Emmanuel Cecchet wrote:
Dan,
My bad, I copy/pasted the hard link in output/copy.source instead of
@abs_build...@.
Here is a complete version of the patch with the fix on output/copy.source.
Emmanuel
Emmanuel,
Thanks for getting that back so quickly. As I said before, it
applies cleanly and passes regression tests. I'm reading through the
changes now. When you get a moment could you send me the patch as a
context diff?
--
--Dan
--
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com
Index: src/test/regress/sql/copy2.sql
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/sql/copy2.sql,v
retrieving revision 1.18
diff -c -r1.18 copy2.sql
*** src/test/regress/sql/copy2.sql 25 Jul 2009 00:07:14 -0000 1.18
--- src/test/regress/sql/copy2.sql 17 Sep 2009 20:59:50 -0000
***************
*** 73,89 ****
\.
-- various COPY options: delimiters, oids, NULL string
! COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
500000,x,45,80,90
500001,x,\x,\\x,\\\x
500002,x,\,,\\\,,\\
\.
! COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
3000;;c;;
\.
! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X';
4000:\X:C:\X:\X
4001:1:empty::
4002:2:null:\X:\X
--- 73,89 ----
\.
-- various COPY options: delimiters, oids, NULL string
! COPY x (b, c, d, e) from stdin (oids, delimiter ',', null 'x');
500000,x,45,80,90
500001,x,\x,\\x,\\\x
500002,x,\,,\\\,,\\
\.
! COPY x from stdin (DELIMITER ';', NULL '');
3000;;c;;
\.
! COPY x from stdin (DELIMITER ':', NULL E'\\X');
4000:\X:C:\X:\X
4001:1:empty::
4002:2:null:\X:\X
***************
*** 108,120 ****
INSERT INTO no_oids (a, b) VALUES (20, 30);
-- should fail
! COPY no_oids FROM stdin WITH OIDS;
! COPY no_oids TO stdout WITH OIDS;
-- check copy out
COPY x TO stdout;
COPY x (c, e) TO stdout;
! COPY x (b, e) TO stdout WITH NULL 'I''m null';
CREATE TEMP TABLE y (
col1 text,
--- 108,120 ----
INSERT INTO no_oids (a, b) VALUES (20, 30);
-- should fail
! COPY no_oids FROM stdin (OIDS);
! COPY no_oids TO stdout (OIDS);
-- check copy out
COPY x TO stdout;
COPY x (c, e) TO stdout;
! COPY x (b, e) TO stdout (NULL 'I''m null');
CREATE TEMP TABLE y (
col1 text,
***************
*** 130,140 ****
COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\';
COPY y TO stdout WITH CSV FORCE QUOTE *;
--test that we read consecutive LFs properly
CREATE TEMP TABLE testnl (a int, b text, c int);
! COPY testnl FROM stdin CSV;
1,"a field with two LFs
inside",2
--- 130,152 ----
COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\';
COPY y TO stdout WITH CSV FORCE QUOTE *;
+ -- Test new 8.5 syntax
+
+ COPY y TO stdout (CSV);
+ COPY y TO stdout (CSV, CSV_QUOTE '''', DELIMITER '|');
+ COPY y TO stdout (CSV, CSV_FORCE_QUOTE (col2), CSV_ESCAPE E'\\');
+ COPY y TO stdout (CSV, CSV_FORCE_QUOTE *);
+
+ \COPY y TO stdout (CSV)
+ \COPY y TO stdout (CSV, CSV_QUOTE '''', DELIMITER '|')
+ \COPY y TO stdout (CSV, CSV_FORCE_QUOTE (col2), CSV_ESCAPE E'\\')
+ \COPY y TO stdout (CSV, CSV_FORCE_QUOTE *)
+
--test that we read consecutive LFs properly
CREATE TEMP TABLE testnl (a int, b text, c int);
! COPY testnl FROM stdin (CSV);
1,"a field with two LFs
inside",2
***************
*** 143,156 ****
-- test end of copy marker
CREATE TEMP TABLE testeoc (a text);
! COPY testeoc FROM stdin CSV;
a\.
\.b
c\.d
"\."
\.
! COPY testeoc TO stdout CSV;
DROP TABLE x, y;
DROP FUNCTION fn_x_before();
--- 155,168 ----
-- test end of copy marker
CREATE TEMP TABLE testeoc (a text);
! COPY testeoc FROM stdin (CSV);
a\.
\.b
c\.d
"\."
\.
! COPY testeoc TO stdout (CSV);
DROP TABLE x, y;
DROP FUNCTION fn_x_before();
Index: src/test/regress/sql/aggregates.sql
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/sql/aggregates.sql,v
retrieving revision 1.15
diff -c -r1.15 aggregates.sql
*** src/test/regress/sql/aggregates.sql 25 Apr 2009 16:44:56 -0000 1.15
--- src/test/regress/sql/aggregates.sql 17 Sep 2009 20:59:50 -0000
***************
*** 104,110 ****
BIT_OR(i4) AS "?"
FROM bitwise_test;
! COPY bitwise_test FROM STDIN NULL 'null';
1 1 1 1 1 B0101
3 3 3 null 2 B0100
7 7 7 3 4 B1100
--- 104,110 ----
BIT_OR(i4) AS "?"
FROM bitwise_test;
! COPY bitwise_test FROM STDIN (NULL 'null');
1 1 1 1 1 B0101
3 3 3 null 2 B0100
7 7 7 3 4 B1100
***************
*** 171,177 ****
BOOL_OR(b3) AS "n"
FROM bool_test;
! COPY bool_test FROM STDIN NULL 'null';
TRUE null FALSE null
FALSE TRUE null null
null TRUE FALSE null
--- 171,177 ----
BOOL_OR(b3) AS "n"
FROM bool_test;
! COPY bool_test FROM STDIN (NULL 'null');
TRUE null FALSE null
FALSE TRUE null null
null TRUE FALSE null
Index: src/test/regress/sql/copyselect.sql
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/sql/copyselect.sql,v
retrieving revision 1.2
diff -c -r1.2 copyselect.sql
*** src/test/regress/sql/copyselect.sql 7 Aug 2008 01:11:52 -0000 1.2
--- src/test/regress/sql/copyselect.sql 17 Sep 2009 20:59:50 -0000
***************
*** 61,67 ****
--
-- Test headers, CSV and quotes
--
! copy (select t from test1 where id = 1) to stdout csv header force quote t;
--
-- Test psql builtins, plain table
--
--- 61,67 ----
--
-- Test headers, CSV and quotes
--
! copy (select t from test1 where id = 1) to stdout (csv, csv_header,
csv_force_quote (t));
--
-- Test psql builtins, plain table
--
Index: src/test/regress/expected/aggregates.out
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/expected/aggregates.out,v
retrieving revision 1.19
diff -c -r1.19 aggregates.out
*** src/test/regress/expected/aggregates.out 25 Apr 2009 16:44:56 -0000
1.19
--- src/test/regress/expected/aggregates.out 17 Sep 2009 20:59:50 -0000
***************
*** 326,332 ****
|
(1 row)
! COPY bitwise_test FROM STDIN NULL 'null';
SELECT
BIT_AND(i2) AS "1",
BIT_AND(i4) AS "1",
--- 326,332 ----
|
(1 row)
! COPY bitwise_test FROM STDIN (NULL 'null');
SELECT
BIT_AND(i2) AS "1",
BIT_AND(i4) AS "1",
***************
*** 401,407 ****
|
(1 row)
! COPY bool_test FROM STDIN NULL 'null';
SELECT
BOOL_AND(b1) AS "f",
BOOL_AND(b2) AS "t",
--- 401,407 ----
|
(1 row)
! COPY bool_test FROM STDIN (NULL 'null');
SELECT
BOOL_AND(b1) AS "f",
BOOL_AND(b2) AS "t",
Index: src/test/regress/expected/copyselect.out
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/expected/copyselect.out,v
retrieving revision 1.2
diff -c -r1.2 copyselect.out
*** src/test/regress/expected/copyselect.out 7 Aug 2008 01:11:52 -0000
1.2
--- src/test/regress/expected/copyselect.out 17 Sep 2009 20:59:50 -0000
***************
*** 93,99 ****
--
-- Test headers, CSV and quotes
--
! copy (select t from test1 where id = 1) to stdout csv header force quote t;
t
"a"
--
--- 93,99 ----
--
-- Test headers, CSV and quotes
--
! copy (select t from test1 where id = 1) to stdout (csv, csv_header,
csv_force_quote (t));
t
"a"
--
Index: src/test/regress/expected/copy2.out
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/expected/copy2.out,v
retrieving revision 1.27
diff -c -r1.27 copy2.out
*** src/test/regress/expected/copy2.out 25 Jul 2009 00:07:14 -0000 1.27
--- src/test/regress/expected/copy2.out 17 Sep 2009 20:59:50 -0000
***************
*** 47,55 ****
ERROR: extra data after last expected column
CONTEXT: COPY x, line 1: "2002 232 40 50 60 70
80"
-- various COPY options: delimiters, oids, NULL string
! COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
! COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X';
-- check results of copy in
SELECT * FROM x;
a | b | c | d | e
--- 47,55 ----
ERROR: extra data after last expected column
CONTEXT: COPY x, line 1: "2002 232 40 50 60 70
80"
-- various COPY options: delimiters, oids, NULL string
! COPY x (b, c, d, e) from stdin (oids, delimiter ',', null 'x');
! COPY x from stdin (DELIMITER ';', NULL '');
! COPY x from stdin (DELIMITER ':', NULL E'\\X');
-- check results of copy in
SELECT * FROM x;
a | b | c | d | e
***************
*** 89,97 ****
INSERT INTO no_oids (a, b) VALUES (5, 10);
INSERT INTO no_oids (a, b) VALUES (20, 30);
-- should fail
! COPY no_oids FROM stdin WITH OIDS;
ERROR: table "no_oids" does not have OIDs
! COPY no_oids TO stdout WITH OIDS;
ERROR: table "no_oids" does not have OIDs
-- check copy out
COPY x TO stdout;
--- 89,97 ----
INSERT INTO no_oids (a, b) VALUES (5, 10);
INSERT INTO no_oids (a, b) VALUES (20, 30);
-- should fail
! COPY no_oids FROM stdin (OIDS);
ERROR: table "no_oids" does not have OIDs
! COPY no_oids TO stdout (OIDS);
ERROR: table "no_oids" does not have OIDs
-- check copy out
COPY x TO stdout;
***************
*** 146,152 ****
stuff after trigger fired
stuff after trigger fired
stuff after trigger fired
! COPY x (b, e) TO stdout WITH NULL 'I''m null';
I'm null before trigger fired
21 before trigger fired
22 before trigger fired
--- 146,152 ----
stuff after trigger fired
stuff after trigger fired
stuff after trigger fired
! COPY x (b, e) TO stdout (NULL 'I''m null');
I'm null before trigger fired
21 before trigger fired
22 before trigger fired
***************
*** 195,207 ****
"Jackson, Sam","\h"
"It is ""perfect""."," "
"",
--test that we read consecutive LFs properly
CREATE TEMP TABLE testnl (a int, b text, c int);
! COPY testnl FROM stdin CSV;
-- test end of copy marker
CREATE TEMP TABLE testeoc (a text);
! COPY testeoc FROM stdin CSV;
! COPY testeoc TO stdout CSV;
a\.
\.b
c\.d
--- 195,240 ----
"Jackson, Sam","\h"
"It is ""perfect""."," "
"",
+ -- Test new 8.5 syntax
+ COPY y TO stdout (CSV);
+ "Jackson, Sam",\h
+ "It is ""perfect"".",
+ "",
+ COPY y TO stdout (CSV, CSV_QUOTE '''', DELIMITER '|');
+ Jackson, Sam|\h
+ It is "perfect".|
+ ''|
+ COPY y TO stdout (CSV, CSV_FORCE_QUOTE (col2), CSV_ESCAPE E'\\');
+ "Jackson, Sam","\\h"
+ "It is \"perfect\"."," "
+ "",
+ COPY y TO stdout (CSV, CSV_FORCE_QUOTE *);
+ "Jackson, Sam","\h"
+ "It is ""perfect""."," "
+ "",
+ \COPY y TO stdout (CSV)
+ "Jackson, Sam",\h
+ "It is ""perfect"".",
+ "",
+ \COPY y TO stdout (CSV, CSV_QUOTE '''', DELIMITER '|')
+ Jackson, Sam|\h
+ It is "perfect".|
+ ''|
+ \COPY y TO stdout (CSV, CSV_FORCE_QUOTE (col2), CSV_ESCAPE E'\\')
+ "Jackson, Sam","\\h"
+ "It is \"perfect\"."," "
+ "",
+ \COPY y TO stdout (CSV, CSV_FORCE_QUOTE *)
+ "Jackson, Sam","\h"
+ "It is ""perfect""."," "
+ "",
--test that we read consecutive LFs properly
CREATE TEMP TABLE testnl (a int, b text, c int);
! COPY testnl FROM stdin (CSV);
-- test end of copy marker
CREATE TEMP TABLE testeoc (a text);
! COPY testeoc FROM stdin (CSV);
! COPY testeoc TO stdout (CSV);
a\.
\.b
c\.d
Index: doc/src/sgml/ref/copy.sgml
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/doc/src/sgml/ref/copy.sgml,v
retrieving revision 1.87
diff -c -r1.87 copy.sgml
*** doc/src/sgml/ref/copy.sgml 5 Sep 2009 23:58:01 -0000 1.87
--- doc/src/sgml/ref/copy.sgml 17 Sep 2009 20:59:50 -0000
***************
*** 24,50 ****
<synopsis>
COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable
class="parameter">column</replaceable> [, ...] ) ]
FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
! [ [ WITH ]
! [ BINARY ]
! [ OIDS ]
! [ DELIMITER [ AS ] '<replaceable
class="parameter">delimiter</replaceable>' ]
! [ NULL [ AS ] '<replaceable class="parameter">null
string</replaceable>' ]
! [ CSV [ HEADER ]
! [ QUOTE [ AS ] '<replaceable
class="parameter">quote</replaceable>' ]
! [ ESCAPE [ AS ] '<replaceable
class="parameter">escape</replaceable>' ]
! [ FORCE NOT NULL <replaceable
class="parameter">column</replaceable> [, ...] ]
COPY { <replaceable class="parameter">tablename</replaceable> [ (
<replaceable class="parameter">column</replaceable> [, ...] ) ] | (
<replaceable class="parameter">query</replaceable> ) }
TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
! [ [ WITH ]
! [ BINARY ]
! [ OIDS ]
! [ DELIMITER [ AS ] '<replaceable
class="parameter">delimiter</replaceable>' ]
! [ NULL [ AS ] '<replaceable class="parameter">null
string</replaceable>' ]
! [ CSV [ HEADER ]
! [ QUOTE [ AS ] '<replaceable
class="parameter">quote</replaceable>' ]
! [ ESCAPE [ AS ] '<replaceable
class="parameter">escape</replaceable>' ]
! [ FORCE QUOTE { <replaceable
class="parameter">column</replaceable> [, ...] | * } ]
</synopsis>
</refsynopsisdiv>
--- 24,47 ----
<synopsis>
COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable
class="parameter">column</replaceable> [, ...] ) ]
FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
! [ ( options [,...] ) ]
COPY { <replaceable class="parameter">tablename</replaceable> [ (
<replaceable class="parameter">column</replaceable> [, ...] ) ] | (
<replaceable class="parameter">query</replaceable> ) }
TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
! [ ( options [,...] ) ]
!
! Currently available options are:
! - BINARY [ <replaceable class="parameter">boolean</replaceable> ]
! - OIDS [ <replaceable class="parameter">boolean</replaceable> ]
! - DELIMITER '<replaceable class="parameter">delimiter</replaceable>'
! - NULL '<replaceable class="parameter">null string</replaceable>'
! - CSV [ <replaceable class="parameter">boolean</replaceable> ]
! - CSV_HEADER [ <replaceable class="parameter">boolean</replaceable> ]
! - CSV_QUOTE '<replaceable class="parameter">quote</replaceable>'
! - CSV_ESCAPE '<replaceable class="parameter">escape</replaceable>'
! - CSV_FORCE_QUOTE { ( <replaceable class="parameter">column</replaceable>
[, ...] ) | * }
! - CSV_FORCE_NOT_NULL ( <replaceable
class="parameter">column</replaceable> [, ...] )
!
</synopsis>
</refsynopsisdiv>
***************
*** 143,148 ****
--- 140,166 ----
</para>
</listitem>
</varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Options</title>
+
+ <variablelist>
+
+ <varlistentry>
+ <term><replaceable class="parameter">boolean</replaceable></term>
+ <listitem>
+ <para>
+ Specifies whether the selected option should be turned on or off.
+ You can write <literal>TRUE</literal>, <literal>ON</>, or
+ <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
+ <literal>OFF</>, or <literal>0</literal> to disable it. The
+ <replaceable class="parameter">boolean</replaceable> value can also
+ be omitted, in which case <literal>TRUE</literal> is assumed.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>BINARY</literal></term>
***************
*** 168,174 ****
</varlistentry>
<varlistentry>
! <term><replaceable class="parameter">delimiter</replaceable></term>
<listitem>
<para>
The single ASCII character that separates columns within each row
--- 186,192 ----
</varlistentry>
<varlistentry>
! <term><literal>DELIMITER</literal></term>
<listitem>
<para>
The single ASCII character that separates columns within each row
***************
*** 179,185 ****
</varlistentry>
<varlistentry>
! <term><replaceable class="parameter">null string</replaceable></term>
<listitem>
<para>
The string that represents a null value. The default is
--- 197,203 ----
</varlistentry>
<varlistentry>
! <term><literal>NULL</literal></term>
<listitem>
<para>
The string that represents a null value. The default is
***************
*** 211,217 ****
</varlistentry>
<varlistentry>
! <term><literal>HEADER</literal></term>
<listitem>
<para>
Specifies that the file contains a header line with the names of each
--- 229,235 ----
</varlistentry>
<varlistentry>
! <term><literal>CSV_HEADER</literal></term>
<listitem>
<para>
Specifies that the file contains a header line with the names of each
***************
*** 222,228 ****
</varlistentry>
<varlistentry>
! <term><replaceable class="parameter">quote</replaceable></term>
<listitem>
<para>
Specifies the ASCII quotation character in <literal>CSV</> mode.
--- 240,246 ----
</varlistentry>
<varlistentry>
! <term><literal>CSV_QUOTE</literal></term>
<listitem>
<para>
Specifies the ASCII quotation character in <literal>CSV</> mode.
***************
*** 232,249 ****
</varlistentry>
<varlistentry>
! <term><replaceable class="parameter">escape</replaceable></term>
<listitem>
<para>
Specifies the ASCII character that should appear before a
! <literal>QUOTE</> data character value in <literal>CSV</> mode.
! The default is the <literal>QUOTE</> value (usually double-quote).
</para>
</listitem>
</varlistentry>
<varlistentry>
! <term><literal>FORCE QUOTE</></term>
<listitem>
<para>
In <literal>CSV</> <command>COPY TO</> mode, forces quoting to be
--- 250,267 ----
</varlistentry>
<varlistentry>
! <term><literal>CSV_ESCAPE</literal></term>
<listitem>
<para>
Specifies the ASCII character that should appear before a
! <literal>CSV_QUOTE</> data character value in <literal>CSV</> mode.
! The default is the <literal>CSV_QUOTE</> value (usually double-quote).
</para>
</listitem>
</varlistentry>
<varlistentry>
! <term><literal>CSV_FORCE_QUOTE</></term>
<listitem>
<para>
In <literal>CSV</> <command>COPY TO</> mode, forces quoting to be
***************
*** 256,262 ****
</varlistentry>
<varlistentry>
! <term><literal>FORCE NOT NULL</></term>
<listitem>
<para>
In <literal>CSV</> <command>COPY FROM</> mode, process each
--- 274,280 ----
</varlistentry>
<varlistentry>
! <term><literal>CSV_FORCE_NOT_NULL</></term>
<listitem>
<para>
In <literal>CSV</> <command>COPY FROM</> mode, process each
***************
*** 295,301 ****
</para>
<para>
! The <literal>BINARY</literal> key word causes all data to be
stored/read as binary format rather than as text. It is
somewhat faster than the normal text mode, but a binary-format
file is less portable across machine architectures and
--- 313,319 ----
</para>
<para>
! The <literal>BINARY</literal> option causes all data to be
stored/read as binary format rather than as text. It is
somewhat faster than the normal text mode, but a binary-format
file is less portable across machine architectures and
***************
*** 538,549 ****
<para>
The values in each record are separated by the <literal>DELIMITER</>
character. If the value contains the delimiter character, the
! <literal>QUOTE</> character, the <literal>NULL</> string, a carriage
return, or line feed character, then the whole value is prefixed and
! suffixed by the <literal>QUOTE</> character, and any occurrence
! within the value of a <literal>QUOTE</> character or the
! <literal>ESCAPE</> character is preceded by the escape character.
! You can also use <literal>FORCE QUOTE</> to force quotes when outputting
non-<literal>NULL</> values in specific columns.
</para>
--- 556,567 ----
<para>
The values in each record are separated by the <literal>DELIMITER</>
character. If the value contains the delimiter character, the
! <literal>CSV_QUOTE</> character, the <literal>NULL</> string, a carriage
return, or line feed character, then the whole value is prefixed and
! suffixed by the <literal>CSV_QUOTE</> character, and any occurrence
! within the value of a <literal>CSV_QUOTE</> character or the
! <literal>CSV_ESCAPE</> character is preceded by the escape character.
! You can also use <literal>CSV_FORCE_QUOTE</> to force quotes when
outputting
non-<literal>NULL</> values in specific columns.
</para>
***************
*** 557,563 ****
settings, a <literal>NULL</> is written as an unquoted empty
string, while an empty string is written with double quotes
(<literal>""</>). Reading values follows similar rules. You can
! use <literal>FORCE NOT NULL</> to prevent <literal>NULL</> input
comparisons for specific columns.
</para>
--- 575,581 ----
settings, a <literal>NULL</> is written as an unquoted empty
string, while an empty string is written with double quotes
(<literal>""</>). Reading values follows similar rules. You can
! use <literal>CSV_FORCE_NOT_NULL</> to prevent <literal>NULL</> input
comparisons for specific columns.
</para>
***************
*** 577,583 ****
<para>
In <literal>CSV</> mode, all characters are significant. A quoted value
surrounded by white space, or any characters other than
! <literal>DELIMITER</>, will include those characters. This can cause
errors if you import data from a system that pads <literal>CSV</>
lines with white space out to some fixed width. If such a situation
arises you might need to preprocess the <literal>CSV</> file to remove
--- 595,601 ----
<para>
In <literal>CSV</> mode, all characters are significant. A quoted value
surrounded by white space, or any characters other than
! <literal>CSV_DELIMITER</>, will include those characters. This can cause
errors if you import data from a system that pads <literal>CSV</>
lines with white space out to some fixed width. If such a situation
arises you might need to preprocess the <literal>CSV</> file to remove
***************
*** 759,765 ****
The following example copies a table to the client
using the vertical bar (<literal>|</literal>) as the field delimiter:
<programlisting>
! COPY country TO STDOUT WITH DELIMITER '|';
</programlisting>
</para>
--- 777,783 ----
The following example copies a table to the client
using the vertical bar (<literal>|</literal>) as the field delimiter:
<programlisting>
! COPY country TO STDOUT (DELIMITER '|');
</programlisting>
</para>
***************
*** 809,814 ****
--- 827,838 ----
0000200 M B A B W E 377 377 377 377 377 377
</programlisting>
</para>
+ <para>
+ Multiple options are separated by a comma like:
+ <programlisting>
+ COPY (SELECT t FROM foo WHERE id = 1) TO STDOUT (CSV, CSV_HEADER,
CSV_FORCE_QUOTE (t));
+ </programlisting>
+ </para>
</refsect1>
<refsect1>
***************
*** 817,823 ****
--- 841,875 ----
<para>
There is no <command>COPY</command> statement in the SQL standard.
</para>
+ <para>
+ The following syntax was used before <productname>PostgreSQL</>
+ version 8.5 and is still supported:
+ <synopsis>
+ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable
class="parameter">column</replaceable> [, ...] ) ]
+ FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
+ [ [ WITH ]
+ [ BINARY ]
+ [ OIDS ]
+ [ DELIMITER [ AS ] '<replaceable
class="parameter">delimiter</replaceable>' ]
+ [ NULL [ AS ] '<replaceable class="parameter">null
string</replaceable>' ]
+ [ CSV [ HEADER ]
+ [ QUOTE [ AS ] '<replaceable
class="parameter">quote</replaceable>' ]
+ [ ESCAPE [ AS ] '<replaceable
class="parameter">escape</replaceable>' ]
+ [ FORCE NOT NULL <replaceable
class="parameter">column</replaceable> [, ...] ]
+ COPY { <replaceable class="parameter">tablename</replaceable> [ (
<replaceable class="parameter">column</replaceable> [, ...] ) ] | (
<replaceable class="parameter">query</replaceable> ) }
+ TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
+ [ [ WITH ]
+ [ BINARY ]
+ [ OIDS ]
+ [ DELIMITER [ AS ] '<replaceable
class="parameter">delimiter</replaceable>' ]
+ [ NULL [ AS ] '<replaceable class="parameter">null
string</replaceable>' ]
+ [ CSV [ HEADER ]
+ [ QUOTE [ AS ] '<replaceable
class="parameter">quote</replaceable>' ]
+ [ ESCAPE [ AS ] '<replaceable
class="parameter">escape</replaceable>' ]
+ [ FORCE QUOTE { <replaceable
class="parameter">column</replaceable> [, ...] | * } ]
+ </synopsis>
+ </para>
<para>
The following syntax was used before <productname>PostgreSQL</>
version 7.3 and is still supported:
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.677
diff -c -r2.677 gram.y
*** src/backend/parser/gram.y 18 Aug 2009 23:40:20 -0000 2.677
--- src/backend/parser/gram.y 17 Sep 2009 20:59:50 -0000
***************
*** 373,378 ****
--- 373,382 ----
%type <node> explain_option_arg
%type <defelt> explain_option_elem
%type <list> explain_option_list
+ %type <str> copy_generic_option_name
+ %type <node> copy_generic_option_arg copy_generic_option_arg_item
+ %type <defelt> copy_generic_option_elem
+ %type <list> copy_generic_option_list copy_generic_option_arg_list
%type <typnam> Typename SimpleTypename ConstTypename
GenericType Numeric opt_float
***************
*** 1934,1947 ****
/*****************************************************************************
*
* QUERY :
! * COPY relname ['(' columnList ')'] FROM/TO file
[WITH options]
! *
! * BINARY, OIDS, and DELIMITERS kept in old
locations
! * for backward compatibility. 2002-06-18
*
* COPY ( SELECT ... ) TO file [WITH options]
! * This form doesn't have the backwards-compatible
option
! * syntax.
*
*****************************************************************************/
--- 1938,1956 ----
/*****************************************************************************
*
* QUERY :
! * New, more generic syntax, supported beginning
with PostgreSQL
! * 8.5. Options are comma-separated.
! * COPY relname ['(' columnList ')'] FROM/TO file
'(' options ')'
*
+ * Older syntax, used from 7.3 to 8.4 and still
supported for
+ * backwards compatibility
+ * COPY relname ['(' columnList ')'] FROM/TO file
[WITH options]
* COPY ( SELECT ... ) TO file [WITH options]
! *
! * Really old syntax, from versions 7.2 and prior:
! * COPY [ BINARY ] table [ WITH OIDS ] FROM/TO file
! * [ [ USING ] DELIMITERS 'delimiter' ] ]
! * [ WITH NULL AS 'null string' ]
*
*****************************************************************************/
***************
*** 2001,2006 ****
--- 2010,2016 ----
copy_opt_list:
copy_opt_list copy_opt_item
{ $$ = lappend($1, $2); }
+ | '(' copy_generic_option_list ')' { $$ =
$2 ; }
| /* EMPTY */
{ $$ = NIL; }
;
***************
*** 2028,2054 ****
}
| HEADER_P
{
! $$ = makeDefElem("header", (Node
*)makeInteger(TRUE));
}
| QUOTE opt_as Sconst
{
! $$ = makeDefElem("quote", (Node
*)makeString($3));
}
| ESCAPE opt_as Sconst
{
! $$ = makeDefElem("escape", (Node
*)makeString($3));
}
| FORCE QUOTE columnList
{
! $$ = makeDefElem("force_quote", (Node
*)$3);
}
| FORCE QUOTE '*'
{
! $$ = makeDefElem("force_quote", (Node
*)makeNode(A_Star));
}
| FORCE NOT NULL_P columnList
{
! $$ = makeDefElem("force_notnull", (Node
*)$4);
}
;
--- 2038,2064 ----
}
| HEADER_P
{
! $$ = makeDefElem("csv_header", (Node
*)makeInteger(TRUE));
}
| QUOTE opt_as Sconst
{
! $$ = makeDefElem("csv_quote", (Node
*)makeString($3));
}
| ESCAPE opt_as Sconst
{
! $$ = makeDefElem("csv_escape", (Node
*)makeString($3));
}
| FORCE QUOTE columnList
{
! $$ = makeDefElem("csv_force_quote",
(Node *)$3);
}
| FORCE QUOTE '*'
{
! $$ = makeDefElem("csv_force_quote",
(Node *)makeNode(A_Star));
}
| FORCE NOT NULL_P columnList
{
! $$ = makeDefElem("csv_force_not_null",
(Node *)$4);
}
;
***************
*** 2084,2089 ****
--- 2094,2146 ----
| /*EMPTY*/
{}
;
+ copy_generic_option_list:
+ copy_generic_option_elem
+ {
+ $$ = list_make1($1);
+ }
+ | copy_generic_option_list ',' copy_generic_option_elem
+ {
+ $$ = lappend($1, $3);
+ }
+ ;
+
+ copy_generic_option_elem:
+ copy_generic_option_name copy_generic_option_arg
+ {
+ $$ = makeDefElem($1, $2);
+ }
+ ;
+
+ copy_generic_option_name:
+ ColLabel
{ $$ = $1; }
+ ;
+
+ copy_generic_option_arg:
+ copy_generic_option_arg_item { $$ =
$1; }
+ | '(' copy_generic_option_arg_list ')' { $$ = (Node *)
$2; }
+ | '*'
{ $$ = (Node *)makeNode(A_Star); }
+ | '(' ')'
{ $$ = NULL; }
+ | /* EMPTY */
{ $$ = NULL; }
+ ;
+
+ copy_generic_option_arg_list:
+ copy_generic_option_arg_item
+ {
+ $$ = list_make1($1);
+ }
+ | copy_generic_option_arg_list ','
copy_generic_option_arg_item
+ {
+ $$ = lappend($1, $3);
+ }
+ ;
+
+ copy_generic_option_arg_item:
+ opt_boolean { $$ = (Node *)
makeString($1); }
+ | ColId_or_Sconst { $$ = (Node *)
makeString($1); }
+ | NumericOnly { $$ = (Node *) $1; }
+ ;
+
/*****************************************************************************
*
Index: src/bin/psql/copy.c
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/bin/psql/copy.c,v
retrieving revision 1.82
diff -c -r1.82 copy.c
*** src/bin/psql/copy.c 7 Aug 2009 20:16:11 -0000 1.82
--- src/bin/psql/copy.c 17 Sep 2009 20:59:50 -0000
***************
*** 26,45 ****
#include "prompt.h"
#include "stringutils.h"
-
/*
* parse_slash_copy
* -- parses \copy command line
*
* The documented syntax is:
* \copy tablename [(columnlist)] from|to filename
* [ with ] [ binary ] [ oids ] [ delimiter [as] char ] [ null [as]
string ]
! * [ csv [ header ] [ quote [ AS ] string ] escape [as] string
* [ force not null column [, ...] | force quote column [, ...] |
* ] ]
*
* \copy ( select stmt ) to filename
* [ with ] [ binary ] [ delimiter [as] char ] [ null [as] string ]
! * [ csv [ header ] [ quote [ AS ] string ] escape [as] string
* [ force quote column [, ...] | * ] ]
*
* Force quote only applies for copy to; force not null only applies for
--- 26,52 ----
#include "prompt.h"
#include "stringutils.h"
/*
* parse_slash_copy
* -- parses \copy command line
*
* The documented syntax is:
+ * Since 8.5:
+ * \copy tablename [(columnlist)] from|to filename [( options )]
+ *
+ * options is a comma separated list of options. Currently supported options:
+ * binary, oids, delimiter, null, csv, csv_header, csv_espace, csv_quote,
+ * csv_force_not_null, csv_force_quote
+ *
+ * Prior 8.5:
* \copy tablename [(columnlist)] from|to filename
* [ with ] [ binary ] [ oids ] [ delimiter [as] char ] [ null [as]
string ]
! * [ csv [ header ] [ quote [ AS ] string ] [ escape [as] string ]
* [ force not null column [, ...] | force quote column [, ...] |
* ] ]
*
* \copy ( select stmt ) to filename
* [ with ] [ binary ] [ delimiter [as] char ] [ null [as] string ]
! * [ csv [ header ] [ quote [ AS ] string ] [ escape [as] string ]
* [ force quote column [, ...] | * ] ]
*
* Force quote only applies for copy to; force not null only applies for
***************
*** 54,74 ****
struct copy_options
{
! char *table;
! char *column_list;
! char *file; /* NULL = stdin/stdout */
! bool psql_inout; /* true = use psql stdin/stdout
*/
! bool from;
! bool binary;
! bool oids;
! bool csv_mode;
! bool header;
! char *delim;
! char *null;
! char *quote;
! char *escape;
! char *force_quote_list;
! char *force_notnull_list;
};
--- 61,71 ----
struct copy_options
{
! char *before_tofrom; /* COPY string before TO/FROM */
! char *after_tofrom; /* COPY string after TO/FROM (options) */
! char *file; /* NULL = stdin/stdout */
! bool psql_inout; /* true if pstdin/pstdout */
! bool from; /* true = from, false = to */
};
***************
*** 77,91 ****
{
if (!ptr)
return;
- free(ptr->table);
- free(ptr->column_list);
free(ptr->file);
! free(ptr->delim);
! free(ptr->null);
! free(ptr->quote);
! free(ptr->escape);
! free(ptr->force_quote_list);
! free(ptr->force_notnull_list);
free(ptr);
}
--- 74,82 ----
{
if (!ptr)
return;
free(ptr->file);
! free(ptr->before_tofrom);
! free(ptr->after_tofrom);
free(ptr);
}
***************
*** 128,143 ****
if (!token)
goto error;
if (pg_strcasecmp(token, "binary") == 0)
{
- result->binary = true;
token = strtokx(NULL, whitespace, ".,()", "\"",
0, false, false, pset.encoding);
if (!token)
goto error;
- }
! result->table = pg_strdup(token);
/* Handle COPY (SELECT) case */
if (token[0] == '(')
--- 119,137 ----
if (!token)
goto error;
+ result->before_tofrom = pg_strdup(token);
+
+ /* The following can be removed when we drop 7.3 syntax support */
if (pg_strcasecmp(token, "binary") == 0)
{
token = strtokx(NULL, whitespace, ".,()", "\"",
0, false, false, pset.encoding);
if (!token)
goto error;
! xstrcat(&result->before_tofrom, " ");
! xstrcat(&result->before_tofrom, token);
! }
/* Handle COPY (SELECT) case */
if (token[0] == '(')
***************
*** 154,208 ****
parens++;
else if (token[0] == ')')
parens--;
! xstrcat(&result->table, " ");
! xstrcat(&result->table, token);
}
}
! token = strtokx(NULL, whitespace, ".,()", "\"",
0, false, false, pset.encoding);
if (!token)
goto error;
- /*
- * strtokx() will not have returned a multi-character token starting
with
- * '.', so we don't need strcmp() here. Likewise for '(', etc, below.
- */
- if (token[0] == '.')
- {
- /* handle schema . table */
- xstrcat(&result->table, token);
- token = strtokx(NULL, whitespace, ".,()", "\"",
- 0, false, false, pset.encoding);
- if (!token)
- goto error;
- xstrcat(&result->table, token);
- token = strtokx(NULL, whitespace, ".,()", "\"",
- 0, false, false, pset.encoding);
- if (!token)
- goto error;
- }
-
if (token[0] == '(')
{
/* handle parenthesized column list */
- result->column_list = pg_strdup(token);
for (;;)
{
! token = strtokx(NULL, whitespace, ".,()", "\"",
! 0, false, false,
pset.encoding);
! if (!token || strchr(".,()", token[0]))
! goto error;
! xstrcat(&result->column_list, token);
! token = strtokx(NULL, whitespace, ".,()", "\"",
0, false, false,
pset.encoding);
if (!token)
goto error;
! xstrcat(&result->column_list, token);
if (token[0] == ')')
break;
- if (token[0] != ',')
- goto error;
}
token = strtokx(NULL, whitespace, ".,()", "\"",
0, false, false, pset.encoding);
--- 148,182 ----
parens++;
else if (token[0] == ')')
parens--;
! xstrcat(&result->before_tofrom, " ");
! xstrcat(&result->before_tofrom, token);
}
}
! token = strtokx(NULL, whitespace, ",()", "\"",
0, false, false, pset.encoding);
if (!token)
goto error;
if (token[0] == '(')
{
+ xstrcat(&result->before_tofrom, " ");
+ xstrcat(&result->before_tofrom, token);
+
/* handle parenthesized column list */
for (;;)
{
! token = strtokx(NULL, whitespace, ")", "\"",
0, false, false,
pset.encoding);
+
if (!token)
goto error;
!
! xstrcat(&result->before_tofrom, " ");
! xstrcat(&result->before_tofrom, token);
!
if (token[0] == ')')
break;
}
token = strtokx(NULL, whitespace, ".,()", "\"",
0, false, false, pset.encoding);
***************
*** 241,394 ****
expand_tilde(&result->file);
}
! token = strtokx(NULL, whitespace, NULL, NULL,
! 0, false, false, pset.encoding);
if (token)
! {
! /*
! * WITH is optional. Also, the backend will allow WITH
followed by
! * nothing, so we do too.
! */
! if (pg_strcasecmp(token, "with") == 0)
! token = strtokx(NULL, whitespace, NULL, NULL,
! 0, false, false,
pset.encoding);
!
! while (token)
! {
! bool fetch_next;
!
! fetch_next = true;
!
! if (pg_strcasecmp(token, "oids") == 0)
! result->oids = true;
! else if (pg_strcasecmp(token, "binary") == 0)
! result->binary = true;
! else if (pg_strcasecmp(token, "csv") == 0)
! result->csv_mode = true;
! else if (pg_strcasecmp(token, "header") == 0)
! result->header = true;
! else if (pg_strcasecmp(token, "delimiter") == 0)
! {
! if (result->delim)
! goto error;
! token = strtokx(NULL, whitespace, NULL, "'",
!
nonstd_backslash, true, false, pset.encoding);
! if (token && pg_strcasecmp(token, "as") == 0)
! token = strtokx(NULL, whitespace, NULL,
"'",
! nonstd_backslash,
true, false, pset.encoding);
! if (token)
! result->delim = pg_strdup(token);
! else
! goto error;
! }
! else if (pg_strcasecmp(token, "null") == 0)
! {
! if (result->null)
! goto error;
! token = strtokx(NULL, whitespace, NULL, "'",
!
nonstd_backslash, true, false, pset.encoding);
! if (token && pg_strcasecmp(token, "as") == 0)
! token = strtokx(NULL, whitespace, NULL,
"'",
! nonstd_backslash,
true, false, pset.encoding);
! if (token)
! result->null = pg_strdup(token);
! else
! goto error;
! }
! else if (pg_strcasecmp(token, "quote") == 0)
! {
! if (result->quote)
! goto error;
! token = strtokx(NULL, whitespace, NULL, "'",
!
nonstd_backslash, true, false, pset.encoding);
! if (token && pg_strcasecmp(token, "as") == 0)
! token = strtokx(NULL, whitespace, NULL,
"'",
! nonstd_backslash,
true, false, pset.encoding);
! if (token)
! result->quote = pg_strdup(token);
! else
! goto error;
! }
! else if (pg_strcasecmp(token, "escape") == 0)
! {
! if (result->escape)
! goto error;
! token = strtokx(NULL, whitespace, NULL, "'",
!
nonstd_backslash, true, false, pset.encoding);
! if (token && pg_strcasecmp(token, "as") == 0)
! token = strtokx(NULL, whitespace, NULL,
"'",
! nonstd_backslash,
true, false, pset.encoding);
! if (token)
! result->escape = pg_strdup(token);
! else
! goto error;
! }
! else if (pg_strcasecmp(token, "force") == 0)
! {
! token = strtokx(NULL, whitespace, ",", "\"",
! 0, false,
false, pset.encoding);
! if (pg_strcasecmp(token, "quote") == 0)
! {
! if (result->force_quote_list)
! goto error;
! /* handle column list */
! fetch_next = false;
! for (;;)
! {
! token = strtokx(NULL,
whitespace, ",", "\"",
!
0, false, false, pset.encoding);
! if (!token || strchr(",",
token[0]))
! goto error;
! if (!result->force_quote_list)
!
result->force_quote_list = pg_strdup(token);
! else
!
xstrcat(&result->force_quote_list, token);
! token = strtokx(NULL,
whitespace, ",", "\"",
!
0, false, false, pset.encoding);
! if (!token || token[0] != ',')
! break;
!
xstrcat(&result->force_quote_list, token);
! }
! }
! else if (pg_strcasecmp(token, "not") == 0)
! {
! if (result->force_notnull_list)
! goto error;
! token = strtokx(NULL, whitespace, ",",
"\"",
! 0,
false, false, pset.encoding);
! if (pg_strcasecmp(token, "null") != 0)
! goto error;
! /* handle column list */
! fetch_next = false;
! for (;;)
! {
! token = strtokx(NULL,
whitespace, ",", "\"",
!
0, false, false, pset.encoding);
! if (!token || strchr(",",
token[0]))
! goto error;
! if (!result->force_notnull_list)
!
result->force_notnull_list = pg_strdup(token);
! else
!
xstrcat(&result->force_notnull_list, token);
! token = strtokx(NULL,
whitespace, ",", "\"",
!
0, false, false, pset.encoding);
! if (!token || token[0] != ',')
! break;
!
xstrcat(&result->force_notnull_list, token);
! }
! }
! else
! goto error;
! }
! else
! goto error;
!
! if (fetch_next)
! token = strtokx(NULL, whitespace, NULL, NULL,
! 0, false,
false, pset.encoding);
! }
! }
free(line);
--- 215,226 ----
expand_tilde(&result->file);
}
! /* Catch the rest of the COPY options */
! token = strtokx(NULL, "", NULL, NULL, 0, false,
! false, pset.encoding);
if (token)
! result->after_tofrom = pg_strdup(token);
free(line);
***************
*** 407,429 ****
/*
- * Handle one of the "string" options of COPY. If the user gave a
quoted
- * string, pass it to the backend as-is; if it wasn't quoted then quote
- * and escape it.
- */
- static void
- emit_copy_option(PQExpBuffer query, const char *keyword, const char *option)
- {
- appendPQExpBufferStr(query, keyword);
- if (option[0] == '\'' ||
- ((option[0] == 'E' || option[0] == 'e') && option[1] == '\''))
- appendPQExpBufferStr(query, option);
- else
- appendStringLiteralConn(query, option, pset.db);
- }
-
-
- /*
* Execute a \copy command (frontend copy). We have to open a file, then
* submit a COPY query to the backend and either feed it data from the
* file or route its response into the file.
--- 239,244 ----
***************
*** 445,498 ****
return false;
initPQExpBuffer(&query);
-
printfPQExpBuffer(&query, "COPY ");
! appendPQExpBuffer(&query, "%s ", options->table);
!
! if (options->column_list)
! appendPQExpBuffer(&query, "%s ", options->column_list);
!
! if (options->from)
! appendPQExpBuffer(&query, "FROM STDIN");
else
! appendPQExpBuffer(&query, "TO STDOUT");
!
!
! if (options->binary)
! appendPQExpBuffer(&query, " BINARY ");
!
! if (options->oids)
! appendPQExpBuffer(&query, " OIDS ");
! if (options->delim)
! emit_copy_option(&query, " DELIMITER ", options->delim);
!
! if (options->null)
! emit_copy_option(&query, " NULL AS ", options->null);
!
! if (options->csv_mode)
! appendPQExpBuffer(&query, " CSV");
!
! if (options->header)
! appendPQExpBuffer(&query, " HEADER");
!
! if (options->quote)
! emit_copy_option(&query, " QUOTE AS ", options->quote);
!
! if (options->escape)
! emit_copy_option(&query, " ESCAPE AS ", options->escape);
!
! if (options->force_quote_list)
! appendPQExpBuffer(&query, " FORCE QUOTE %s",
options->force_quote_list);
! if (options->force_notnull_list)
! appendPQExpBuffer(&query, " FORCE NOT NULL %s",
options->force_notnull_list);
! if (options->file)
! canonicalize_path(options->file);
! if (options->from)
{
if (options->file)
copystream = fopen(options->file, PG_BINARY_R);
--- 260,288 ----
return false;
initPQExpBuffer(&query);
printfPQExpBuffer(&query, "COPY ");
! if ((options->file == NULL) && (options->psql_inout == false))
! { /* Send the query as is, it's a simple COPY operation */
! appendPQExpBuffer(&query, "%s", pg_strdup(args));
! }
else
! { /* Replace the filename with STDIN/STDOUT */
! appendPQExpBuffer(&query, "%s ", options->before_tofrom);
! if (options->from)
! appendPQExpBuffer(&query, " FROM STDIN ");
! else
! appendPQExpBuffer(&query, " TO STDOUT ");
! if (options->after_tofrom)
! appendPQExpBuffer(&query, "%s", options->after_tofrom);
! if (options->file)
! canonicalize_path(options->file);
! }
! if (options->from)
{
if (options->file)
copystream = fopen(options->file, PG_BINARY_R);
***************
*** 504,511 ****
else
{
if (options->file)
! copystream = fopen(options->file,
! options->binary ?
PG_BINARY_W : "w");
else if (!options->psql_inout)
copystream = pset.queryFout;
else
--- 294,300 ----
else
{
if (options->file)
! copystream = fopen(options->file, PG_BINARY_W);
else if (!options->psql_inout)
copystream = pset.queryFout;
else
Index: src/test/regress/output/copy.source
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/output/copy.source,v
retrieving revision 1.13
diff -c -r1.13 copy.source
*** src/test/regress/output/copy.source 21 Aug 2007 01:11:31 -0000 1.13
--- src/test/regress/output/copy.source 17 Sep 2009 20:59:50 -0000
***************
*** 71,73 ****
--- 71,119 ----
c1,"col with , comma","col with "" quote"
1,a,1
2,b,2
+ -- Repeat the above tests with the new 8.5 option syntax
+ copy copytest to '@abs_builddir@/results/copytest.csv' (csv);
+ truncate copytest2;
+ copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv);
+ select * from copytest except select * from copytest2;
+ style | test | filler
+ -------+------+--------
+ (0 rows)
+
+ truncate copytest2;
+ copy copytest to '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''',
csv_escape E'\\');
+ copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv, csv_quote
'''', csv_escape E'\\');
+ select * from copytest except select * from copytest2;
+ style | test | filler
+ -------+------+--------
+ (0 rows)
+
+ truncate copytest3;
+ copy copytest3 from stdin (csv, csv_header);
+ copy copytest3 to stdout (csv, csv_header);
+ c1,"col with , comma","col with "" quote"
+ 1,a,1
+ 2,b,2
+ -- Repeat the above tests with the new 8.5 option syntax from psql
+ \copy copytest to '@abs_builddir@/results/copytest.csv' (csv)
+ truncate copytest2;
+ \copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv)
+ select * from copytest except select * from copytest2;
+ style | test | filler
+ -------+------+--------
+ (0 rows)
+
+ truncate copytest2;
+ \copy copytest to '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''',
csv_escape E'\\')
+ \copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv, csv_quote
'''', csv_escape E'\\')
+ select * from copytest except select * from copytest2;
+ style | test | filler
+ -------+------+--------
+ (0 rows)
+
+ truncate copytest3;
+ \copy copytest3 from stdin (csv, csv_header)
+ \copy copytest3 to stdout (csv, csv_header)
+ c1,"col with , comma","col with "" quote"
+ 1,a,1
+ 2,b,2
Index: src/test/regress/input/copy.source
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/input/copy.source,v
retrieving revision 1.15
diff -c -r1.15 copy.source
*** src/test/regress/input/copy.source 21 Aug 2007 01:11:31 -0000 1.15
--- src/test/regress/input/copy.source 17 Sep 2009 20:59:50 -0000
***************
*** 107,109 ****
--- 107,164 ----
copy copytest3 to stdout csv header;
+ -- Repeat the above tests with the new 8.5 option syntax
+
+ copy copytest to '@abs_builddir@/results/copytest.csv' (csv);
+
+ truncate copytest2;
+
+ copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv);
+
+ select * from copytest except select * from copytest2;
+
+ truncate copytest2;
+
+ copy copytest to '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''',
csv_escape E'\\');
+
+ copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv, csv_quote
'''', csv_escape E'\\');
+
+ select * from copytest except select * from copytest2;
+
+ truncate copytest3;
+
+ copy copytest3 from stdin (csv, csv_header);
+ this is just a line full of junk that would error out if parsed
+ 1,a,1
+ 2,b,2
+ \.
+
+ copy copytest3 to stdout (csv, csv_header);
+
+ -- Repeat the above tests with the new 8.5 option syntax from psql
+
+ \copy copytest to '@abs_builddir@/results/copytest.csv' (csv)
+
+ truncate copytest2;
+
+ \copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv)
+
+ select * from copytest except select * from copytest2;
+
+ truncate copytest2;
+
+ \copy copytest to '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''',
csv_escape E'\\')
+
+ \copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv, csv_quote
'''', csv_escape E'\\')
+
+ select * from copytest except select * from copytest2;
+
+ truncate copytest3;
+
+ \copy copytest3 from stdin (csv, csv_header)
+ this is just a line full of junk that would error out if parsed
+ 1,a,1
+ 2,b,2
+ \.
+
+ \copy copytest3 to stdout (csv, csv_header)
Index: src/backend/commands/copy.c
===================================================================
RCS file: /home/manu/cvsrepo/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.316
diff -c -r1.316 copy.c
*** src/backend/commands/copy.c 29 Jul 2009 20:56:18 -0000 1.316
--- src/backend/commands/copy.c 17 Sep 2009 20:59:50 -0000
***************
*** 25,30 ****
--- 25,31 ----
#include "catalog/namespace.h"
#include "catalog/pg_type.h"
#include "commands/copy.h"
+ #include "commands/defrem.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "libpq/libpq.h"
***************
*** 745,751 ****
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->binary = intVal(defel->arg);
}
else if (strcmp(defel->defname, "oids") == 0)
{
--- 746,752 ----
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->binary = defGetBoolean(defel);
}
else if (strcmp(defel->defname, "oids") == 0)
{
***************
*** 753,759 ****
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->oids = intVal(defel->arg);
}
else if (strcmp(defel->defname, "delimiter") == 0)
{
--- 754,760 ----
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->oids = defGetBoolean(defel);
}
else if (strcmp(defel->defname, "delimiter") == 0)
{
***************
*** 761,767 ****
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->delim = strVal(defel->arg);
}
else if (strcmp(defel->defname, "null") == 0)
{
--- 762,768 ----
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->delim = defGetString(defel);
}
else if (strcmp(defel->defname, "null") == 0)
{
***************
*** 769,775 ****
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->null_print = strVal(defel->arg);
}
else if (strcmp(defel->defname, "csv") == 0)
{
--- 770,776 ----
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->null_print = defGetString(defel);
}
else if (strcmp(defel->defname, "csv") == 0)
{
***************
*** 777,809 ****
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->csv_mode = intVal(defel->arg);
}
! else if (strcmp(defel->defname, "header") == 0)
{
if (cstate->header_line)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->header_line = intVal(defel->arg);
}
! else if (strcmp(defel->defname, "quote") == 0)
{
if (cstate->quote)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->quote = strVal(defel->arg);
}
! else if (strcmp(defel->defname, "escape") == 0)
{
if (cstate->escape)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->escape = strVal(defel->arg);
}
! else if (strcmp(defel->defname, "force_quote") == 0)
{
if (force_quote || force_quote_all)
ereport(ERROR,
--- 778,810 ----
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->csv_mode = defGetBoolean(defel);
}
! else if (strcmp(defel->defname, "csv_header") == 0)
{
if (cstate->header_line)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->header_line = defGetBoolean(defel);
}
! else if (strcmp(defel->defname, "csv_quote") == 0)
{
if (cstate->quote)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->quote = defGetString(defel);
}
! else if (strcmp(defel->defname, "csv_escape") == 0)
{
if (cstate->escape)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! cstate->escape = defGetString(defel);
}
! else if (strcmp(defel->defname, "csv_force_quote") == 0)
{
if (force_quote || force_quote_all)
ereport(ERROR,
***************
*** 811,830 ****
errmsg("conflicting or
redundant options")));
if (defel->arg && IsA(defel->arg, A_Star))
force_quote_all = true;
! else
force_quote = (List *) defel->arg;
}
! else if (strcmp(defel->defname, "force_notnull") == 0)
{
if (force_notnull)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! force_notnull = (List *) defel->arg;
}
else
! elog(ERROR, "option \"%s\" not recognized",
! defel->defname);
}
/* Check for incompatible options */
--- 812,856 ----
errmsg("conflicting or
redundant options")));
if (defel->arg && IsA(defel->arg, A_Star))
force_quote_all = true;
! else if (defel->arg && IsA(defel->arg, List))
! {
! ListCell *lc;
!
force_quote = (List *) defel->arg;
+ foreach (lc, force_quote)
+ {
+ if (!IsA(lfirst(lc), String))
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("argument to
option \"%s\" must be a list of column names",
+
defel->defname)));
+ }
+ }
+ else
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("argument to option \"%s\" must
be a list of column names",
+ defel->defname)));
}
! else if (strcmp(defel->defname, "csv_force_not_null") == 0)
{
if (force_notnull)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or
redundant options")));
! if (defel->arg && IsA(defel->arg, List))
! force_notnull = (List *) defel->arg;
! else
! ereport(ERROR,
!
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg("argument to option \"%s\" must
be a list",
! defel->defname)));
}
else
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("option \"%s\" not recognized",
! defel->defname)));
}
/* Check for incompatible options */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
