From 923413ac6d31826bd599711962ce9cb7d51a997f Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 21 Sep 2009 20:10:21 +0000 Subject: [PATCH] Define a new, more extensible syntax for COPY options. This is intentionally similar to the recently revised syntax for EXPLAIN options, ie, (name value, ...). The old syntax is still supported for backwards compatibility, but we intend that any options added in future will be provided only in the new syntax. Robert Haas, Emmanuel Cecchet --- doc/src/sgml/ref/copy.sgml | 223 +++++++++++++++++----------- src/backend/commands/copy.c | 75 ++++++---- src/backend/commands/define.c | 4 +- src/backend/parser/gram.y | 89 ++++++++--- src/test/regress/expected/copy2.out | 33 ++++ src/test/regress/sql/copy2.sql | 12 ++ 6 files changed, 304 insertions(+), 132 deletions(-) diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 0e8cb5567b..57a0a6e5f1 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -1,5 +1,5 @@ @@ -24,27 +24,23 @@ PostgreSQL documentation COPY table_name [ ( column [, ...] ) ] FROM { 'filename' | STDIN } - [ [ WITH ] - [ BINARY ] - [ OIDS ] - [ DELIMITER [ AS ] 'delimiter' ] - [ NULL [ AS ] 'null string' ] - [ CSV [ HEADER ] - [ QUOTE [ AS ] 'quote' ] - [ ESCAPE [ AS ] 'escape' ] - [ FORCE NOT NULL column [, ...] ] + [ [ WITH ] ( option [, ...] ) ] COPY { table_name [ ( column [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } - [ [ WITH ] - [ BINARY ] - [ OIDS ] - [ DELIMITER [ AS ] 'delimiter' ] - [ NULL [ AS ] 'null string' ] - [ CSV [ HEADER ] - [ QUOTE [ AS ] 'quote' ] - [ ESCAPE [ AS ] 'escape' ] - [ FORCE QUOTE { column [, ...] | * } ] + [ [ WITH ] ( option [, ...] ) ] + +where option can be one of: + + FORMAT format_name + OIDS [ boolean ] + DELIMITER 'delimiter_character' + NULL 'null_string' + HEADER [ boolean ] + QUOTE 'quote_character' + ESCAPE 'escape_character' + FORCE_QUOTE { ( column [, ...] ) | * } + FORCE_NOT_NULL ( column [, ...] ) @@ -120,8 +116,8 @@ COPY { table_name [ ( The absolute path name of the input or output file. Windows users - might need to use an E'' string and double backslashes - used as path separators. + might need to use an E'' string and double any backslashes + used in the path name. @@ -145,12 +141,28 @@ COPY { table_name [ ( - BINARY + boolean - Causes all data to be stored or read in binary format rather - than as text. You cannot specify the , - , or + + + + + FORMAT + + + Selects the data format to be read or written: + text, + csv (Comma Separated Values), + or binary. + The default is text. @@ -168,25 +180,28 @@ COPY { table_name [ ( - delimiter + DELIMITER - The single ASCII character that separates columns within each row - (line) of the file. The default is a tab character in text mode, - a comma in CSV mode. + Specifies the character that separates columns within each row + (line) of the file. The default is a tab character in text format, + a comma in CSV format. + This must be a single one-byte character. + This option is not allowed when using binary format. - null string + NULL - The string that represents a null value. The default is - \N (backslash-N) in text mode, and an unquoted empty - string in CSV mode. You might prefer an - empty string even in text mode for cases where you don't want to + Specifies the string that represents a null value. The default is + \N (backslash-N) in text format, and an unquoted empty + string in CSV format. You might prefer an + empty string even in text format for cases where you don't want to distinguish nulls from empty strings. + This option is not allowed when using binary format. @@ -201,15 +216,6 @@ COPY { table_name [ ( - - CSV - - - Selects Comma Separated Value (CSV) mode. - - - - HEADER @@ -217,52 +223,61 @@ COPY { table_name [ ( CSV format. - quote + QUOTE - Specifies the ASCII quotation character in CSV mode. + Specifies the quoting character to be used when a data value is quoted. The default is double-quote. + This must be a single one-byte character. + This option is allowed only when using CSV format. - escape + ESCAPE - Specifies the ASCII character that should appear before a - QUOTE data character value in CSV mode. - The default is the QUOTE value (usually double-quote). + Specifies the character that should appear before a + data character that matches the QUOTE value. + The default is the same as the QUOTE value (so that + the quoting character is doubled if it appears in the data). + This must be a single one-byte character. + This option is allowed only when using CSV format. - FORCE QUOTE + FORCE_QUOTE - In CSV COPY TO mode, forces quoting to be + Forces quoting to be used for all non-NULL values in each specified column. NULL output is never quoted. If * is specified, non-NULL values will be quoted in all columns. + This option is allowed only in COPY TO, and only when + using CSV format. - FORCE NOT NULL + FORCE_NOT_NULL - In CSV COPY FROM mode, process each - specified column as though it were quoted and hence not a - NULL value. For the default null string in - CSV mode (''), this causes missing - values to be input as zero-length strings. + Do not match the specified columns' values against the null string. + In the default case where the null string is empty, this means that + empty values will be read as zero-length strings rather than nulls, + even when they are not quoted. + This option is allowed only in COPY FROM, and only when + using CSV format. @@ -293,18 +308,6 @@ COPY count viewname) TO .... - - The BINARY 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 - PostgreSQL versions. - Also, the binary format is very data type specific; for example - it will not work to output binary data from a smallint column - and read it into an integer column, even though that would work - fine in text format. - - You must have select privilege on the table whose values are read by COPY TO, and @@ -390,8 +393,7 @@ COPY count Text Format - When COPY is used without the BINARY - or CSV options, + When the text format is used, the data read or written is a text file with one line per table row. Columns in a row are separated by the delimiter character. The column values themselves are strings generated by the @@ -527,10 +529,10 @@ COPY count CSV Format - This format is used for importing and exporting the Comma + This format option is used for importing and exporting the Comma Separated Value (CSV) file format used by many other - programs, such as spreadsheets. Instead of the escaping used by - PostgreSQL's standard text mode, it + programs, such as spreadsheets. Instead of the escaping rules used by + PostgreSQL's standard text format, it produces and recognizes the common CSV escaping mechanism. @@ -542,7 +544,7 @@ COPY count suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character. - You can also use FORCE QUOTE to force quotes when outputting + You can also use FORCE_QUOTE to force quotes when outputting non-NULL values in specific columns. @@ -556,7 +558,7 @@ COPY count default settings, a NULL is written as an unquoted empty string, while an empty string data value is written with double quotes (""). Reading values follows similar rules. You can - use FORCE NOT NULL to prevent NULL input + use FORCE_NOT_NULL to prevent NULL input comparisons for specific columns. @@ -574,7 +576,7 @@ COPY count - In CSV mode, all characters are significant. A quoted value + In CSV format, all characters are significant. A quoted value surrounded by white space, or any characters other than DELIMITER, will include those characters. This can cause errors if you import data from a system that pads CSV @@ -587,9 +589,9 @@ COPY count - CSV mode will both recognize and produce CSV files with quoted + CSV format will both recognize and produce CSV files with quoted values containing embedded carriage returns and line feeds. Thus - the files are not strictly one line per table row like text-mode + the files are not strictly one line per table row like text-format files. @@ -610,12 +612,30 @@ COPY count Binary Format - The file format used for COPY BINARY changed in - PostgreSQL 7.4. The new format consists + The binary format option causes all data to be + stored/read as binary format rather than as text. It is + somewhat faster than the text and CSV formats, + but a binary-format file is less portable across machine architectures and + PostgreSQL versions. + Also, the binary format is very data type specific; for example + it will not work to output binary data from a smallint column + and read it into an integer column, even though that would work + fine in text format. + + + + The binary file format consists of a file header, zero or more tuples containing the row data, and - a file trailer. Headers and data are now in network byte order. + a file trailer. Headers and data are in network byte order. + + + PostgreSQL releases before 7.4 used a + different binary file format. + + + File Header @@ -710,7 +730,7 @@ There is no alignment padding or any other extra data between fields. -Presently, all data values in a COPY BINARY file are +Presently, all data values in a binary-format file are assumed to be in binary format (format code one). It is anticipated that a future extension might add a header field that allows per-column format codes to be specified. @@ -758,7 +778,7 @@ OIDs to be shown as null if that ever proves desirable. The following example copies a table to the client using the vertical bar (|) as the field delimiter: -COPY country TO STDOUT WITH DELIMITER '|'; +COPY country TO STDOUT (DELIMITER '|'); @@ -817,6 +837,41 @@ ZW ZIMBABWE There is no COPY statement in the SQL standard. + + The following syntax was used before PostgreSQL + version 8.5 and is still supported: + + +COPY table_name [ ( column [, ...] ) ] + FROM { 'filename' | STDIN } + [ [ WITH ] + [ BINARY ] + [ OIDS ] + [ DELIMITER [ AS ] 'delimiter' ] + [ NULL [ AS ] 'null string' ] + [ CSV [ HEADER ] + [ QUOTE [ AS ] 'quote' ] + [ ESCAPE [ AS ] 'escape' ] + [ FORCE NOT NULL column [, ...] ] + +COPY { table_name [ ( column [, ...] ) ] | ( query ) } + TO { 'filename' | STDOUT } + [ [ WITH ] + [ BINARY ] + [ OIDS ] + [ DELIMITER [ AS ] 'delimiter' ] + [ NULL [ AS ] 'null string' ] + [ CSV [ HEADER ] + [ QUOTE [ AS ] 'quote' ] + [ ESCAPE [ AS ] 'escape' ] + [ FORCE QUOTE { column [, ...] | * } ] + + + Note that in this syntax, BINARY and CSV are + treated as independent keywords, not as arguments of a FORMAT + option. + + The following syntax was used before PostgreSQL version 7.3 and is still supported: diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 21f7b94d54..5d60df2873 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.316 2009/07/29 20:56:18 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.317 2009/09/21 20:10:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -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" @@ -723,6 +724,7 @@ DoCopy(const CopyStmt *stmt, const char *queryString) List *force_quote = NIL; List *force_notnull = NIL; bool force_quote_all = false; + bool format_specified = false; AclMode required_access = (is_from ? ACL_INSERT : ACL_SELECT); AclMode relPerms; AclMode remainingPerms; @@ -739,13 +741,25 @@ DoCopy(const CopyStmt *stmt, const char *queryString) { DefElem *defel = (DefElem *) lfirst(option); - if (strcmp(defel->defname, "binary") == 0) + if (strcmp(defel->defname, "format") == 0) { - if (cstate->binary) + char *fmt = defGetString(defel); + + if (format_specified) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->binary = intVal(defel->arg); + format_specified = true; + if (strcmp(fmt, "text") == 0) + /* default format */ ; + else if (strcmp(fmt, "csv") == 0) + cstate->csv_mode = true; + else if (strcmp(fmt, "binary") == 0) + cstate->binary = true; + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("COPY format \"%s\" not recognized", fmt))); } else if (strcmp(defel->defname, "oids") == 0) { @@ -753,7 +767,7 @@ DoCopy(const CopyStmt *stmt, const char *queryString) 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 +775,7 @@ DoCopy(const CopyStmt *stmt, const char *queryString) 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,15 +783,7 @@ DoCopy(const CopyStmt *stmt, const char *queryString) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->null_print = strVal(defel->arg); - } - else if (strcmp(defel->defname, "csv") == 0) - { - if (cstate->csv_mode) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("conflicting or redundant options"))); - cstate->csv_mode = intVal(defel->arg); + cstate->null_print = defGetString(defel); } else if (strcmp(defel->defname, "header") == 0) { @@ -785,7 +791,7 @@ DoCopy(const CopyStmt *stmt, const char *queryString) 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) { @@ -793,7 +799,7 @@ DoCopy(const CopyStmt *stmt, const char *queryString) 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) { @@ -801,7 +807,7 @@ DoCopy(const CopyStmt *stmt, const char *queryString) 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) { @@ -811,33 +817,44 @@ DoCopy(const CopyStmt *stmt, const char *queryString) 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)) force_quote = (List *) defel->arg; + 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, "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 of column names", + 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 */ + /* + * Check for incompatible options (must do these two before inserting + * defaults) + */ if (cstate->binary && cstate->delim) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("cannot specify DELIMITER in BINARY mode"))); - if (cstate->binary && cstate->csv_mode) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("cannot specify CSV in BINARY mode"))); - if (cstate->binary && cstate->null_print) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), diff --git a/src/backend/commands/define.c b/src/backend/commands/define.c index 2fd919dd54..da16be4e36 100644 --- a/src/backend/commands/define.c +++ b/src/backend/commands/define.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/define.c,v 1.105 2009/07/26 23:34:17 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/define.c,v 1.106 2009/09/21 20:10:21 tgl Exp $ * * DESCRIPTION * The "DefineFoo" routines take the parse tree and pick out the @@ -88,6 +88,8 @@ defGetString(DefElem *def) return TypeNameToString((TypeName *) def->arg); case T_List: return NameListToString((List *) def->arg); + case T_A_Star: + return pstrdup("*"); default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(def->arg)); } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index ebf5b5d645..81d57f65fb 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.677 2009/08/18 23:40:20 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.678 2009/09/21 20:10:21 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -373,6 +373,10 @@ static TypeName *TableFuncTypeName(List *columns); %type explain_option_arg %type explain_option_elem %type explain_option_list +%type copy_generic_opt_arg copy_generic_opt_arg_list_item +%type copy_generic_opt_elem +%type copy_generic_opt_list copy_generic_opt_arg_list +%type copy_options %type Typename SimpleTypename ConstTypename GenericType Numeric opt_float @@ -1934,19 +1938,23 @@ ClosePortalStmt: /***************************************************************************** * * QUERY : - * COPY relname ['(' columnList ')'] FROM/TO file [WITH options] + * COPY relname [(columnList)] FROM/TO file [WITH] [(options)] + * COPY ( SELECT ... ) TO file [WITH] [(options)] * - * BINARY, OIDS, and DELIMITERS kept in old locations - * for backward compatibility. 2002-06-18 + * In the preferred syntax the options are comma-separated + * and use generic identifiers instead of keywords. The pre-8.5 + * syntax had a hard-wired, space-separated set of 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' ] + * This option placement is not supported with COPY (SELECT...). * *****************************************************************************/ CopyStmt: COPY opt_binary qualified_name opt_column_list opt_oids - copy_from copy_file_name copy_delimiter opt_with copy_opt_list + copy_from copy_file_name copy_delimiter opt_with copy_options { CopyStmt *n = makeNode(CopyStmt); n->relation = $3; @@ -1967,8 +1975,7 @@ CopyStmt: COPY opt_binary qualified_name opt_column_list opt_oids n->options = list_concat(n->options, $10); $$ = (Node *)n; } - | COPY select_with_parens TO copy_file_name opt_with - copy_opt_list + | COPY select_with_parens TO copy_file_name opt_with copy_options { CopyStmt *n = makeNode(CopyStmt); n->relation = NULL; @@ -1997,18 +2004,20 @@ copy_file_name: | STDOUT { $$ = NULL; } ; +copy_options: copy_opt_list { $$ = $1; } + | '(' copy_generic_opt_list ')' { $$ = $2; } + ; - +/* old COPY option syntax */ copy_opt_list: copy_opt_list copy_opt_item { $$ = lappend($1, $2); } | /* EMPTY */ { $$ = NIL; } ; - copy_opt_item: BINARY { - $$ = makeDefElem("binary", (Node *)makeInteger(TRUE)); + $$ = makeDefElem("format", (Node *)makeString("binary")); } | OIDS { @@ -2024,7 +2033,7 @@ copy_opt_item: } | CSV { - $$ = makeDefElem("csv", (Node *)makeInteger(TRUE)); + $$ = makeDefElem("format", (Node *)makeString("csv")); } | HEADER_P { @@ -2048,16 +2057,16 @@ copy_opt_item: } | FORCE NOT NULL_P columnList { - $$ = makeDefElem("force_notnull", (Node *)$4); + $$ = makeDefElem("force_not_null", (Node *)$4); } ; -/* The following exist for backward compatibility */ +/* The following exist for backward compatibility with very old versions */ opt_binary: BINARY { - $$ = makeDefElem("binary", (Node *)makeInteger(TRUE)); + $$ = makeDefElem("format", (Node *)makeString("binary")); } | /*EMPTY*/ { $$ = NULL; } ; @@ -2071,7 +2080,6 @@ opt_oids: ; copy_delimiter: - /* USING DELIMITERS kept for backward compatibility. 2002-06-15 */ opt_using DELIMITERS Sconst { $$ = makeDefElem("delimiter", (Node *)makeString($3)); @@ -2084,6 +2092,51 @@ opt_using: | /*EMPTY*/ {} ; +/* new COPY option syntax */ +copy_generic_opt_list: + copy_generic_opt_elem + { + $$ = list_make1($1); + } + | copy_generic_opt_list ',' copy_generic_opt_elem + { + $$ = lappend($1, $3); + } + ; + +copy_generic_opt_elem: + ColLabel copy_generic_opt_arg + { + $$ = makeDefElem($1, $2); + } + ; + +copy_generic_opt_arg: + opt_boolean { $$ = (Node *) makeString($1); } + | ColId_or_Sconst { $$ = (Node *) makeString($1); } + | NumericOnly { $$ = (Node *) $1; } + | '*' { $$ = (Node *) makeNode(A_Star); } + | '(' copy_generic_opt_arg_list ')' { $$ = (Node *) $2; } + | /* EMPTY */ { $$ = NULL; } + ; + +copy_generic_opt_arg_list: + copy_generic_opt_arg_list_item + { + $$ = list_make1($1); + } + | copy_generic_opt_arg_list ',' copy_generic_opt_arg_list_item + { + $$ = lappend($1, $3); + } + ; + +/* beware of emitting non-string list elements here; see commands/define.c */ +copy_generic_opt_arg_list_item: + opt_boolean { $$ = (Node *) makeString($1); } + | ColId_or_Sconst { $$ = (Node *) makeString($1); } + ; + /***************************************************************************** * diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 5f52d6ee6c..5f921dda8a 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -195,6 +195,39 @@ COPY y TO stdout WITH CSV FORCE QUOTE *; "Jackson, Sam","\h" "It is ""perfect""."," " "", +-- Repeat above tests with new 8.5 option syntax +COPY y TO stdout (FORMAT CSV); +"Jackson, Sam",\h +"It is ""perfect"".", +"", +COPY y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|'); +Jackson, Sam|\h +It is "perfect".| +''| +COPY y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\'); +"Jackson, Sam","\\h" +"It is \"perfect\"."," " +"", +COPY y TO stdout (FORMAT CSV, FORCE_QUOTE *); +"Jackson, Sam","\h" +"It is ""perfect""."," " +"", +\copy y TO stdout (FORMAT CSV) +"Jackson, Sam",\h +"It is ""perfect"".", +"", +\copy y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|') +Jackson, Sam|\h +It is "perfect".| +''| +\copy y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\') +"Jackson, Sam","\\h" +"It is \"perfect\"."," " +"", +\copy y TO stdout (FORMAT 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; diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql index 9dee93c14c..ba0b0a62ad 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -130,6 +130,18 @@ COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|'; COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\'; COPY y TO stdout WITH CSV FORCE QUOTE *; +-- Repeat above tests with new 8.5 option syntax + +COPY y TO stdout (FORMAT CSV); +COPY y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|'); +COPY y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\'); +COPY y TO stdout (FORMAT CSV, FORCE_QUOTE *); + +\copy y TO stdout (FORMAT CSV) +\copy y TO stdout (FORMAT CSV, QUOTE '''', DELIMITER '|') +\copy y TO stdout (FORMAT CSV, FORCE_QUOTE (col2), ESCAPE E'\\') +\copy y TO stdout (FORMAT CSV, FORCE_QUOTE *) + --test that we read consecutive LFs properly CREATE TEMP TABLE testnl (a int, b text, c int); -- 2.40.0