This patch adds the possibility to use the "header" option when using COPY with
the text format. A todo entry was opened for this and I updated the tests and
the documentation.
This was previously discussed at
https://www.postgresql.org/message-id/flat/CACfv%2BpJ31tesLvncJyP24quo8AE%2BM0GP6p6MEpwPv6yV8%3DsVHQ%40mail.gmail.com
Greetings,
Rémi
---
doc/src/sgml/ref/copy.sgml | 3 +-
src/backend/commands/copy.c | 11 ++++---
src/test/regress/input/copy.source | 46 +++++++++++++++++++----------
src/test/regress/output/copy.source | 41 +++++++++++++++----------
4 files changed, 64 insertions(+), 37 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index a99f8155e4..c335320786 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -270,7 +270,8 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
Specifies that the file contains a header line with the names of each
column in the file. On output, the first line contains the column
names from the table, and on input, the first line is ignored.
- This option is allowed only when using <literal>CSV</literal> format.
+ This option is allowed only when using <literal>CSV</literal> or
+ <literal>text</literal> format.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 40a8ec1abd..44bf73423c 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1365,10 +1365,10 @@ ProcessCopyOptions(ParseState *pstate,
errmsg("COPY delimiter cannot be \"%s\"", cstate->delim)));
/* Check header */
- if (!cstate->csv_mode && cstate->header_line)
+ if (cstate->binary && cstate->header_line)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("COPY HEADER available only in CSV mode")));
+ errmsg("COPY HEADER available only in CSV and text mode")));
/* Check quote */
if (!cstate->csv_mode && cstate->quote != NULL)
@@ -2100,8 +2100,11 @@ CopyTo(CopyState cstate)
colname = NameStr(TupleDescAttr(tupDesc, attnum - 1)->attname);
- CopyAttributeOutCSV(cstate, colname, false,
- list_length(cstate->attnumlist) == 1);
+ if (cstate->csv_mode)
+ CopyAttributeOutCSV(cstate, colname, false,
+ list_length(cstate->attnumlist) == 1);
+ else
+ CopyAttributeOutText(cstate, colname);
}
CopySendEndOfRow(cstate);
diff --git a/src/test/regress/input/copy.source b/src/test/regress/input/copy.source
index a1d529ad36..0e08ceb844 100644
--- a/src/test/regress/input/copy.source
+++ b/src/test/regress/input/copy.source
@@ -87,52 +87,66 @@ ANALYZE bt_f8_heap;
ANALYZE array_op_test;
ANALYZE array_index_op_test;
+-- test header line feature
+
+create temp table copytest (
+ c1 int,
+ "col with tabulation: " text);
+
+copy copytest from stdin (header);
+this is just a line full of junk that would error out if parsed
+1 a
+2 b
+\.
+
+copy copytest to stdout (header);
+
--- test copying in CSV mode with various styles
--- of embedded line ending characters
-create temp table copytest (
+create temp table copytest2 (
style text,
test text,
filler int);
-insert into copytest values('DOS',E'abc\r\ndef',1);
-insert into copytest values('Unix',E'abc\ndef',2);
-insert into copytest values('Mac',E'abc\rdef',3);
-insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
+insert into copytest2 values('DOS',E'abc\r\ndef',1);
+insert into copytest2 values('Unix',E'abc\ndef',2);
+insert into copytest2 values('Mac',E'abc\rdef',3);
+insert into copytest2 values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
-copy copytest to '@abs_builddir@/results/copytest.csv' csv;
+copy copytest2 to '@abs_builddir@/results/copytest.csv' csv;
-create temp table copytest2 (like copytest);
+create temp table copytest3 (like copytest2);
-copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
+copy copytest3 from '@abs_builddir@/results/copytest.csv' csv;
-select * from copytest except select * from copytest2;
+select * from copytest2 except select * from copytest3;
-truncate copytest2;
+truncate copytest3;
--- same test but with an escape char different from quote char
-copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
+copy copytest2 to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
-copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
+copy copytest3 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
-select * from copytest except select * from copytest2;
+select * from copytest2 except select * from copytest3;
-- test header line feature
-create temp table copytest3 (
+create temp table copytest4 (
c1 int,
"col with , comma" text,
"col with "" quote" int);
-copy copytest3 from stdin csv header;
+copy copytest4 from stdin 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 header;
+copy copytest4 to stdout csv header;
-- test copy from with a partitioned table
create table parted_copytest (
diff --git a/src/test/regress/output/copy.source b/src/test/regress/output/copy.source
index 938d3551da..7f864d77b2 100644
--- a/src/test/regress/output/copy.source
+++ b/src/test/regress/output/copy.source
@@ -58,40 +58,49 @@ ANALYZE bt_txt_heap;
ANALYZE bt_f8_heap;
ANALYZE array_op_test;
ANALYZE array_index_op_test;
+-- test header line feature
+create temp table copytest (
+ c1 int,
+ "col with tabulation: " text);
+copy copytest from stdin (header);
+copy copytest to stdout (header);
+c1 col with tabulation: \t
+1 a
+2 b
--- test copying in CSV mode with various styles
--- of embedded line ending characters
-create temp table copytest (
+create temp table copytest2 (
style text,
test text,
filler int);
-insert into copytest values('DOS',E'abc\r\ndef',1);
-insert into copytest values('Unix',E'abc\ndef',2);
-insert into copytest values('Mac',E'abc\rdef',3);
-insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
-copy copytest to '@abs_builddir@/results/copytest.csv' csv;
-create temp table copytest2 (like copytest);
-copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
-select * from copytest except select * from copytest2;
+insert into copytest2 values('DOS',E'abc\r\ndef',1);
+insert into copytest2 values('Unix',E'abc\ndef',2);
+insert into copytest2 values('Mac',E'abc\rdef',3);
+insert into copytest2 values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
+copy copytest2 to '@abs_builddir@/results/copytest.csv' csv;
+create temp table copytest3 (like copytest2);
+copy copytest3 from '@abs_builddir@/results/copytest.csv' csv;
+select * from copytest2 except select * from copytest3;
style | test | filler
-------+------+--------
(0 rows)
-truncate copytest2;
+truncate copytest3;
--- same test but with an escape char different from quote char
-copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
-copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
-select * from copytest except select * from copytest2;
+copy copytest2 to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
+copy copytest3 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
+select * from copytest2 except select * from copytest3;
style | test | filler
-------+------+--------
(0 rows)
-- test header line feature
-create temp table copytest3 (
+create temp table copytest4 (
c1 int,
"col with , comma" text,
"col with "" quote" int);
-copy copytest3 from stdin csv header;
-copy copytest3 to stdout csv header;
+copy copytest4 from stdin csv header;
+copy copytest4 to stdout csv header;
c1,"col with , comma","col with "" quote"
1,a,1
2,b,2
