Re: generic copy options - Mailing list pgsql-hackers
| From | Emmanuel Cecchet |
|---|---|
| Subject | Re: generic copy options |
| Date | |
| Msg-id | 4AB250B5.1020903@asterdata.com Whole thread Raw |
| In response to | Re: generic copy options (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: generic copy options
|
| List | pgsql-hackers |
Tom Lane wrote: > I wonder though if we couldn't simplify matters. Offhand it seems to me > that psql doesn't need to validate the command's syntax fully. All it > really needs to do is find the target filename and replace it with > STDIN/STDOUT. Could we have it just treat the remainder of the line > literally, and not worry about the details of what the options might be? > Let the backend worry about throwing an error if they're bad. > New version with the simplified psql. Still supports the 7.3 syntax. I am going to look into porting the other COPY enhancements (error logging and autopartitioning) on this implementation. We might come up with new ideas for the documentation side of things with more options. manu -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com ### Eclipse Workspace Patch 1.0 #P Postgres8.5-COPY 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 -u -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 15:04:06 -0000 @@ -73,17 +73,17 @@ \. -- various COPY options: delimiters, oids, NULL string -COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x'; +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 WITH DELIMITER AS ';' NULL AS ''; +COPY x from stdin (DELIMITER ';', NULL ''); 3000;;c;; \. -COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X'; +COPY x from stdin (DELIMITER ':', NULL E'\\X'); 4000:\X:C:\X:\X 4001:1:empty:: 4002:2:null:\X:\X @@ -108,13 +108,13 @@ 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; +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 WITH NULL 'I''m null'; +COPY x (b, e) TO stdout (NULL 'I''m null'); CREATE TEMP TABLE y ( col1 text, @@ -130,11 +130,23 @@ 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; +COPY testnl FROM stdin (CSV); 1,"a field with two LFs inside",2 @@ -143,14 +155,14 @@ -- test end of copy marker CREATE TEMP TABLE testeoc (a text); -COPY testeoc FROM stdin CSV; +COPY testeoc FROM stdin (CSV); a\. \.b c\.d "\." \. -COPY testeoc TO stdout CSV; +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 -u -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 15:04:06 -0000 @@ -104,7 +104,7 @@ BIT_OR(i4) AS "?" FROM bitwise_test; -COPY bitwise_test FROM STDIN NULL 'null'; +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,7 +171,7 @@ BOOL_OR(b3) AS "n" FROM bool_test; -COPY bool_test FROM STDIN NULL 'null'; +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 -u -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 15:04:06 -0000 @@ -61,7 +61,7 @@ -- -- Test headers, CSV and quotes -- -copy (select t from test1 where id = 1) to stdout csv header force quote t; +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 -u -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 15:04:06 -0000 @@ -326,7 +326,7 @@ | (1 row) -COPY bitwise_test FROM STDIN NULL 'null'; +COPY bitwise_test FROM STDIN (NULL 'null'); SELECT BIT_AND(i2) AS "1", BIT_AND(i4) AS "1", @@ -401,7 +401,7 @@ | (1 row) -COPY bool_test FROM STDIN NULL 'null'; +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 -u -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 15:04:06 -0000 @@ -93,7 +93,7 @@ -- -- Test headers, CSV and quotes -- -copy (select t from test1 where id = 1) to stdout csv header force quote t; +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 -u -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 15:04:06 -0000 @@ -47,9 +47,9 @@ 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'; +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,9 +89,9 @@ 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; +COPY no_oids FROM stdin (OIDS); ERROR: table "no_oids" does not have OIDs -COPY no_oids TO stdout WITH OIDS; +COPY no_oids TO stdout (OIDS); ERROR: table "no_oids" does not have OIDs -- check copy out COPY x TO stdout; @@ -146,7 +146,7 @@ stuff after trigger fired stuff after trigger fired stuff after trigger fired -COPY x (b, e) TO stdout WITH NULL 'I''m null'; +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,13 +195,46 @@ "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; +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; +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 -u -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 15:04:06 -0000 @@ -24,27 +24,24 @@ <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> [, ...] ] + [ ( options [,...] ) ] 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> [, ...] | * } ] + [ ( 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,6 +140,27 @@ </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,7 +186,7 @@ </varlistentry> <varlistentry> - <term><replaceable class="parameter">delimiter</replaceable></term> + <term><literal>DELIMITER</literal></term> <listitem> <para> The single ASCII character that separates columns within each row @@ -179,7 +197,7 @@ </varlistentry> <varlistentry> - <term><replaceable class="parameter">null string</replaceable></term> + <term><literal>NULL</literal></term> <listitem> <para> The string that represents a null value. The default is @@ -211,7 +229,7 @@ </varlistentry> <varlistentry> - <term><literal>HEADER</literal></term> + <term><literal>CSV_HEADER</literal></term> <listitem> <para> Specifies that the file contains a header line with the names of each @@ -222,7 +240,7 @@ </varlistentry> <varlistentry> - <term><replaceable class="parameter">quote</replaceable></term> + <term><literal>CSV_QUOTE</literal></term> <listitem> <para> Specifies the ASCII quotation character in <literal>CSV</> mode. @@ -232,18 +250,18 @@ </varlistentry> <varlistentry> - <term><replaceable class="parameter">escape</replaceable></term> + <term><literal>CSV_ESCAPE</literal></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). + <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>FORCE QUOTE</></term> + <term><literal>CSV_FORCE_QUOTE</></term> <listitem> <para> In <literal>CSV</> <command>COPY TO</> mode, forces quoting to be @@ -256,7 +274,7 @@ </varlistentry> <varlistentry> - <term><literal>FORCE NOT NULL</></term> + <term><literal>CSV_FORCE_NOT_NULL</></term> <listitem> <para> In <literal>CSV</> <command>COPY FROM</> mode, process each @@ -295,7 +313,7 @@ </para> <para> - The <literal>BINARY</literal> key word causes all data to be + 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,12 +556,12 @@ <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 + <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>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 + 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,7 +575,7 @@ 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 + use <literal>CSV_FORCE_NOT_NULL</> to prevent <literal>NULL</> input comparisons for specific columns. </para> @@ -577,7 +595,7 @@ <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 + <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,7 +777,7 @@ 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 '|'; +COPY country TO STDOUT (DELIMITER '|'); </programlisting> </para> @@ -809,6 +827,12 @@ 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,7 +841,35 @@ <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 -u -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 15:04:06 -0000 @@ -373,6 +373,10 @@ %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,14 +1938,19 @@ /***************************************************************************** * * QUERY : - * COPY relname ['(' columnList ')'] FROM/TO file [WITH options] - * - * BINARY, OIDS, and DELIMITERS kept in old locations - * for backward compatibility. 2002-06-18 + * 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] - * This form doesn't have the backwards-compatible option - * syntax. + * + * 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,6 +2010,7 @@ copy_opt_list: copy_opt_list copy_opt_item { $$ = lappend($1, $2); } + | '(' copy_generic_option_list ')' { $$ = $2 ; } | /* EMPTY */ { $$ = NIL; } ; @@ -2028,27 +2038,27 @@ } | HEADER_P { - $$ = makeDefElem("header", (Node *)makeInteger(TRUE)); + $$ = makeDefElem("csv_header", (Node *)makeInteger(TRUE)); } | QUOTE opt_as Sconst { - $$ = makeDefElem("quote", (Node *)makeString($3)); + $$ = makeDefElem("csv_quote", (Node *)makeString($3)); } | ESCAPE opt_as Sconst { - $$ = makeDefElem("escape", (Node *)makeString($3)); + $$ = makeDefElem("csv_escape", (Node *)makeString($3)); } | FORCE QUOTE columnList { - $$ = makeDefElem("force_quote", (Node *)$3); + $$ = makeDefElem("csv_force_quote", (Node *)$3); } | FORCE QUOTE '*' { - $$ = makeDefElem("force_quote", (Node *)makeNode(A_Star)); + $$ = makeDefElem("csv_force_quote", (Node *)makeNode(A_Star)); } | FORCE NOT NULL_P columnList { - $$ = makeDefElem("force_notnull", (Node *)$4); + $$ = makeDefElem("csv_force_not_null", (Node *)$4); } ; @@ -2084,6 +2094,53 @@ | /*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 -u -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 15:04:06 -0000 @@ -26,20 +26,27 @@ #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 + * [ 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 + * [ 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,21 +61,11 @@ 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; + 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,15 +74,9 @@ { 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->before_tofrom); + free(ptr->after_tofrom); free(ptr); } @@ -128,16 +119,19 @@ 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) { - result->binary = true; token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); if (!token) goto error; - } - result->table = pg_strdup(token); + xstrcat(&result->before_tofrom, " "); + xstrcat(&result->before_tofrom, token); + } /* Handle COPY (SELECT) case */ if (token[0] == '(') @@ -154,55 +148,35 @@ parens++; else if (token[0] == ')') parens--; - xstrcat(&result->table, " "); - xstrcat(&result->table, token); + xstrcat(&result->before_tofrom, " "); + xstrcat(&result->before_tofrom, token); } } - token = strtokx(NULL, whitespace, ".,()", "\"", + 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] == '(') { + xstrcat(&result->before_tofrom, " "); + xstrcat(&result->before_tofrom, token); + /* 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, ".,()", "\"", + token = strtokx(NULL, whitespace, ")", "\"", 0, false, false, pset.encoding); + if (!token) goto error; - xstrcat(&result->column_list, token); + + xstrcat(&result->before_tofrom, " "); + xstrcat(&result->before_tofrom, token); + if (token[0] == ')') break; - if (token[0] != ',') - goto error; } token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); @@ -241,154 +215,12 @@ expand_tilde(&result->file); } - token = strtokx(NULL, whitespace, NULL, NULL, - 0, false, false, pset.encoding); + /* Catch the rest of the COPY options */ + token = strtokx(NULL, "", 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); - } - } + result->after_tofrom = pg_strdup(token); free(line); @@ -407,23 +239,6 @@ /* - * 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. @@ -445,54 +260,29 @@ 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"); + 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 - appendPQExpBuffer(&query, "TO STDOUT"); - - - if (options->binary) - appendPQExpBuffer(&query, " BINARY "); - - if (options->oids) - appendPQExpBuffer(&query, " OIDS "); + { /* Replace the filename with STDIN/STDOUT */ + appendPQExpBuffer(&query, "%s ", options->before_tofrom); - 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->from) + appendPQExpBuffer(&query, " FROM STDIN "); + else + appendPQExpBuffer(&query, " TO STDOUT "); - if (options->force_notnull_list) - appendPQExpBuffer(&query, " FORCE NOT NULL %s", options->force_notnull_list); + if (options->after_tofrom) + appendPQExpBuffer(&query, "%s", options->after_tofrom); - if (options->file) - canonicalize_path(options->file); + if (options->file) + canonicalize_path(options->file); + } - if (options->from) + if (options->from) { if (options->file) copystream = fopen(options->file, PG_BINARY_R); @@ -504,8 +294,7 @@ else { if (options->file) - copystream = fopen(options->file, - options->binary ? PG_BINARY_W : "w"); + 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 -u -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 15:04:06 -0000 @@ -71,3 +71,49 @@ 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 '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv); +truncate copytest2; +copy copytest2 from '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv); +select * from copytest except select * from copytest2; + style | test | filler +-------+------+-------- +(0 rows) + +truncate copytest2; +copy copytest to '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv, csv_quote '''', csv_escapeE'\\'); +copy copytest2 from '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/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 '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv) +truncate copytest2; +\copy copytest2 from '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv) +select * from copytest except select * from copytest2; + style | test | filler +-------+------+-------- +(0 rows) + +truncate copytest2; +\copy copytest to '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv, csv_quote '''', csv_escapeE'\\') +\copy copytest2 from '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/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 -u -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 15:04:06 -0000 @@ -107,3 +107,58 @@ 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 -u -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 15:04:06 -0000 @@ -25,6 +25,7 @@ #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,7 +746,7 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->binary = intVal(defel->arg); + cstate->binary = defGetBoolean(defel); } else if (strcmp(defel->defname, "oids") == 0) { @@ -753,7 +754,7 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->oids = intVal(defel->arg); + cstate->oids = defGetBoolean(defel); } else if (strcmp(defel->defname, "delimiter") == 0) { @@ -761,7 +762,7 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->delim = strVal(defel->arg); + cstate->delim = defGetString(defel); } else if (strcmp(defel->defname, "null") == 0) { @@ -769,7 +770,7 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->null_print = strVal(defel->arg); + cstate->null_print = defGetString(defel); } else if (strcmp(defel->defname, "csv") == 0) { @@ -777,33 +778,33 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->csv_mode = intVal(defel->arg); + cstate->csv_mode = defGetBoolean(defel); } - else if (strcmp(defel->defname, "header") == 0) + 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 = intVal(defel->arg); + cstate->header_line = defGetBoolean(defel); } - else if (strcmp(defel->defname, "quote") == 0) + else if (strcmp(defel->defname, "csv_quote") == 0) { if (cstate->quote) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->quote = strVal(defel->arg); + cstate->quote = defGetString(defel); } - else if (strcmp(defel->defname, "escape") == 0) + else if (strcmp(defel->defname, "csv_escape") == 0) { if (cstate->escape) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->escape = strVal(defel->arg); + cstate->escape = defGetString(defel); } - else if (strcmp(defel->defname, "force_quote") == 0) + else if (strcmp(defel->defname, "csv_force_quote") == 0) { if (force_quote || force_quote_all) ereport(ERROR, @@ -811,20 +812,45 @@ errmsg("conflicting or redundant options"))); if (defel->arg && IsA(defel->arg, A_Star)) force_quote_all = true; - else + 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, "force_notnull") == 0) + else if (strcmp(defel->defname, "csv_force_not_null") == 0) { if (force_notnull) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - force_notnull = (List *) defel->arg; + 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 - elog(ERROR, "option \"%s\" not recognized", - defel->defname); + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("option \"%s\" not recognized", + defel->defname))); } /* Check for incompatible options */
pgsql-hackers by date: