| -- |
| -- This test suite tests COPY code that is unique to greenplum db. |
| -- |
| -- ###################################################### |
| -- different distribution columns and distribution types |
| -- ###################################################### |
| CREATE TABLE copy_regression_hash1(a text, b text, c text); |
| CREATE TABLE copy_regression_hash2(a text, b text, c text); |
| CREATE TABLE copy_regression_hash3(a varchar, b int, c int, d text); |
| CREATE TABLE copy_regression_hash4(a int[], b text); |
| CREATE TABLE copy_regression_hash5(a text[][]); |
| CREATE TABLE copy_regression_hash6(a int[], b text[]); |
| CREATE TABLE copy_regression_hash7(a text,b text); |
| -- single key, not first |
| COPY copy_regression_hash1 from stdin; |
| COPY copy_regression_hash1(b,c,a) from stdin; |
| SELECT * FROM copy_regression_hash1; |
| a | b | c |
| --------+--------+-------- |
| a data | b data | c data |
| a data | b data | c data |
| (2 rows) |
| |
| -- two keys |
| COPY copy_regression_hash2 from stdin; |
| COPY copy_regression_hash2(b,c,a) from stdin; |
| SELECT * FROM copy_regression_hash2; |
| a | b | c |
| --------+--------+-------- |
| a data | b data | c data |
| a data | b data | c data |
| (2 rows) |
| |
| -- three keys |
| COPY copy_regression_hash3 from stdin; |
| COPY copy_regression_hash3(c,d,b,a) from stdin; |
| COPY copy_regression_hash3(a,c) from stdin; |
| COPY copy_regression_hash3(d) from stdin; |
| SELECT * FROM copy_regression_hash3; |
| a | b | c | d |
| --------+---+---+-------- |
| a data | 2 | 3 | d data |
| a data | 2 | 3 | d data |
| a data | | 3 | |
| | | | d data |
| (4 rows) |
| |
| -- hash on int array |
| COPY copy_regression_hash4 from stdin; |
| SELECT * FROM copy_regression_hash4 ORDER BY a; |
| a | b |
| ------------------+---------------------------- |
| {1,2,3,4,5} | hashed on an integer array |
| {10,20,30,40,50} | hashed on an integer array |
| (2 rows) |
| |
| -- hash on 2 dim varchar array |
| COPY copy_regression_hash5 from stdin; |
| SELECT * FROM copy_regression_hash5 ORDER BY a; |
| a |
| ------------------------------------------------- |
| {{hashing,on},{"two dimentional","text array"}} |
| (1 row) |
| |
| -- hash on int array and varchar array |
| COPY copy_regression_hash6 from stdin; |
| SELECT * FROM copy_regression_hash6 ORDER BY a; |
| a | b |
| -------------+---------------------------------------- |
| {1,2,3,4,5} | {hashing,on,intarray,and,varchararray} |
| (1 row) |
| |
| -- hash randomly |
| COPY copy_regression_hash7 from stdin; |
| SELECT * FROM copy_regression_hash7; |
| a | b |
| --------+-------- |
| a data | b data |
| a data | b data |
| (2 rows) |
| |
| DROP TABLE copy_regression_hash1; |
| DROP TABLE copy_regression_hash2; |
| DROP TABLE copy_regression_hash3; |
| DROP TABLE copy_regression_hash4; |
| DROP TABLE copy_regression_hash5; |
| DROP TABLE copy_regression_hash6; |
| DROP TABLE copy_regression_hash7; |
| -- ###################################################### |
| -- default values and default functions |
| -- ###################################################### |
| CREATE TABLE copy_regression_default1(a text not null default 'a default data', b text not null default 'b default data') ; |
| CREATE TABLE copy_regression_default2(a text, b serial, c text) ; |
| CREATE TABLE copy_regression_default3(a serial, b text, c text) ; |
| -- constant defaults on key and non key attributes |
| COPY copy_regression_default1(a) from stdin; |
| COPY copy_regression_default1(b) from stdin; |
| SELECT * FROM copy_regression_default1; |
| a | b |
| ----------------+---------------- |
| a default data | b data |
| a data | b default data |
| (2 rows) |
| |
| -- non constant default on non hash key in both text and csv |
| COPY copy_regression_default2(a,c) from stdin; |
| COPY copy_regression_default2(a,c) from stdin csv; |
| SELECT * FROM copy_regression_default2; |
| a | b | c |
| --------+---+--------- |
| a data | 1 | c data |
| a data | 2 | c data |
| a data | 3 | c data |
| a data | 4 | c data |
| a data | 5 | c data |
| a data | 6 | c data |
| (6 rows) |
| |
| -- non constant default on hash key in both text and csv |
| COPY copy_regression_default3(b,c) from stdin; |
| COPY copy_regression_default3(b,c) from stdin csv; |
| SELECT * FROM copy_regression_default2; |
| a | b | c |
| --------+---+--------- |
| a data | 1 | c data |
| a data | 2 | c data |
| a data | 3 | c data |
| a data | 4 | c data |
| a data | 5 | c data |
| a data | 6 | c data |
| (6 rows) |
| |
| DROP TABLE copy_regression_default1; |
| DROP TABLE copy_regression_default2; |
| DROP TABLE copy_regression_default3; |
| -- ###################################################### |
| -- COPY OUT |
| -- ###################################################### |
| CREATE TABLE copy_regression_out1(a text, b varchar, c int[], d bool) ; |
| -- populating table with data for copy out tests. NOTE: since we can't control the order |
| -- of rows COPY OUT produces from the segdb, we must have only one row for each test table. |
| COPY copy_regression_out1 from stdin null 'nullval'; |
| -- copy out default parameters text format.. |
| COPY copy_regression_out1 to stdout; |
| a copyout data line 2 \N {2,2,2} t |
| -- copy out '|' delimiter 'mynull' null.. |
| COPY copy_regression_out1 to stdout with delimiter '|' null 'mynull'; |
| a copyout data line 2|mynull|{2,2,2}|t |
| -- copy out '|' delimiter "i'm null" null.. |
| COPY copy_regression_out1 to stdout with delimiter '|' null 'i''m null'; |
| a copyout data line 2|i'm null|{2,2,2}|t |
| -- copy out default parameters csv format.. |
| COPY copy_regression_out1 to stdout with csv; |
| a copyout data line 2,,"{2,2,2}",t |
| -- copy out default parameters csv format with header.. |
| COPY copy_regression_out1 to stdout with csv header; |
| a,b,c,d |
| a copyout data line 2,,"{2,2,2}",t |
| -- copy out partial and mixed column list (c,a,b).. |
| COPY copy_regression_out1(c,a,b) to stdout; |
| {2,2,2} a copyout data line 2 \N |
| -- copy out csv default quotes and escapes and special cases.. |
| COPY copy_regression_out1 to stdout with csv quote ''''; -- escape should be the same as quote |
| a copyout data line 2,,'{2,2,2}',t |
| COPY copy_regression_out1 to stdout with csv escape ''''; |
| a copyout data line 2,,"{2,2,2}",t |
| COPY copy_regression_out1 to stdout with csv quote '''' escape E'\\'; -- make sure E'' is accepted |
| a copyout data line 2,,'{2,2,2}',t |
| DROP TABLE copy_regression_out1; |
| -- ###################################################### |
| -- Zero column table |
| -- ###################################################### |
| CREATE TABLE copy_regression_nocol(); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry. |
| -- copy in and out of zero column table.. |
| COPY copy_regression_nocol from stdin; |
| COPY copy_regression_nocol from stdin; |
| ERROR: extra data after last expected column |
| CONTEXT: COPY copy_regression_nocol, line 1: "we should get an "extra data" error here" |
| COPY copy_regression_nocol from stdin with csv; |
| COPY copy_regression_nocol from stdin with csv; -- should fail |
| ERROR: extra data after last expected column |
| CONTEXT: COPY copy_regression_nocol, line 1: "we should get an "extra data" error here" |
| COPY copy_regression_nocol to stdout; |
| COPY copy_regression_nocol to stdout with csv; |
| DROP TABLE copy_regression_nocol; |
| -- ###################################################### |
| -- Various text format escape and default format tests |
| -- ###################################################### |
| -- for text format |
| CREATE TABLE copy_regression_text1(a varchar ,b varchar, c text, d text, e text) ; |
| CREATE TABLE copy_regression_text2(a varchar ,b varchar, c text, d text, e text) ; |
| CREATE TABLE copy_regression_text3(a varchar ,b varchar, c text, d text, e text) ; |
| -- column order tests |
| COPY copy_regression_text1 from stdin; |
| COPY copy_regression_text1(a,b,c,d,e) from stdin; |
| COPY copy_regression_text1(e,d,c,b,a) from stdin; |
| COPY copy_regression_text1(c,a,b,e,d) from stdin; |
| COPY copy_regression_text1(a,c) from stdin; |
| SELECT * FROM copy_regression_text1; |
| a | b | c | d | e |
| -------------+-------------+-------------+-------------+------------- |
| a text data | b text data | c text data | d text data | e text data |
| a text data | b text data | c text data | d text data | e text data |
| a text data | b text data | c text data | d text data | e text data |
| a text data | b text data | c text data | d text data | e text data |
| a text data | | c text data | | |
| (5 rows) |
| |
| -- null print tests |
| COPY copy_regression_text2 from stdin; |
| COPY copy_regression_text2 from stdin with null 'nullvalue'; |
| COPY copy_regression_text2 from stdin with delimiter '|' null ''; |
| COPY copy_regression_text2 from stdin with delimiter '|' null 'i''m null'; |
| SELECT * FROM copy_regression_text2; |
| a | b | c | d | e |
| -------------+-------------+-------------+---+------------- |
| a text data | | c text data | | e text data |
| a text data | | c text data | | |
| a text data | | c text data | | e text data |
| | b text data | c text data | | e text data |
| a text data | b text data | | | e text data |
| (5 rows) |
| |
| -- escape tests |
| COPY copy_regression_text3 from stdin with delimiter '|' escape '#'; |
| COPY copy_regression_text3 from stdin with delimiter '|' escape 'off'; |
| COPY copy_regression_text3 from stdin with delimiter '|'; |
| COPY copy_regression_text3 from stdin with delimiter '|'; |
| ERROR: missing data for column "b" |
| CONTEXT: COPY copy_regression_text3, line 2: "and another one\" |
| COPY copy_regression_text3 from stdin with delimiter '|'; |
| SELECT * FROM copy_regression_text3 ORDER BY b,a; |
| a | b | c | d | e |
| ------------------------------------+---+-------------+-------------+-------------- |
| the at sign: @ | 1 | c text data | d text data | e text data |
| an embedded delimiter | character | 2 | c text data | d text data | e text data |
| a single backslash \ in col a | 3 | c text data | d text data | e text data |
| a single backslash \ in col a | 4 | c text data | d text data | e text data |
| c:\\file\data\neew\path | 5 | c text data | d text data | e text data |
| the at sign: @ | 6 | c text data | d text data | e text data |
| an embedded linefeed sequence +| 7 | c text data | d text data | e text data |
| in column a | | | | |
| a single backslash \ in col a | 8 | c text data | d text data | e text data |
| (8 rows) |
| |
| DROP TABLE copy_regression_text1; |
| DROP TABLE copy_regression_text2; |
| DROP TABLE copy_regression_text3; |
| -- ###################################################### |
| -- Various text format escape and default format tests |
| -- ###################################################### |
| -- for csv format |
| CREATE TABLE copy_regression_csv1(a varchar ,b varchar, c text, d text, e text) ; |
| CREATE TABLE copy_regression_csv2(a varchar ,b varchar, c text, d text, e text) ; |
| CREATE TABLE copy_regression_csv3(a varchar ,b varchar, c text, d text, e text) ; |
| -- column order tests |
| COPY copy_regression_csv1 from stdin with csv; |
| COPY copy_regression_csv1(a,b,c,d,e) from stdin with csv; |
| COPY copy_regression_csv1(e,d,c,b,a) from stdin with csv; |
| COPY copy_regression_csv1(c,a,b,e,d) from stdin with csv; |
| COPY copy_regression_csv1(a,c) from stdin with csv; |
| SELECT * FROM copy_regression_csv1; |
| a | b | c | d | e |
| -------------+-------------+-------------+-------------+------------- |
| a csv data | b csv data | c csv data | d csv data | e csv data |
| a csv data | b csv data | c csv data | d csv data | e csv data |
| a csv data | b csv data | c csv data | d csv data | e csv data |
| a csv data | b csv data | c csv data | d csv data | e csv data |
| a csv data | | c csv data | | |
| (5 rows) |
| |
| -- null print tests |
| COPY copy_regression_csv2 from stdin with null E'\\N' csv ; |
| COPY copy_regression_csv2 from stdin with null 'nullvalue' csv; |
| COPY copy_regression_csv2 from stdin with delimiter '|' null '' csv; |
| SELECT * FROM copy_regression_csv2; |
| a | b | c | d | e |
| -------------+-------------+-------------+---+------------- |
| a csv data | | c csv data | | e csv data |
| a csv data | | c csv data | | |
| a csv data | | c csv data | | e csv data |
| | b csv data | c csv data | | e csv data |
| (4 rows) |
| |
| -- escape tests |
| COPY copy_regression_csv3 from stdin with csv escape E'\\'; |
| COPY copy_regression_csv3 from stdin with delimiter E'\t' csv; --default quote and escape - " |
| COPY copy_regression_csv3 from stdin with delimiter '|' csv quote ';' escape '*'; |
| -- check defaults |
| COPY copy_regression_csv3 from stdin with csv quote ''''; -- escape should be the same as quote |
| COPY copy_regression_csv3 from stdin with csv escape ''''; |
| COPY copy_regression_csv3 from stdin with csv quote '''' escape E'\\'; -- make sure E'' is accepted |
| SELECT * FROM copy_regression_csv3 ORDER BY b; |
| a | b | c | d | e |
| ---------------------------------------------------+----+-------------+-------------+-------------- |
| an embedded delimiter (comma), is printed | 01 | c csv data | d csv data | e csv data |
| an embedded quote (doubleq)" is printed | 02 | c csv data | d csv data | e csv data |
| an embedded escape \ is printed | 03 | c csv data | d csv data | e csv data |
| an embedded line feed +| 04 | c csv data | d csv data | e csv data |
| is printed | | | | |
| an embedded delimiter (tab) is printed | 05 | c csv data | d csv data | e csv data |
| an embedded quote or escape (doubleq)" is printed | 06 | c csv data | d csv data | e csv data |
| an embedded line feed +| 07 | c csv data | d csv data | e csv data |
| is printed | | | | |
| an embedded delimiter (pipe)| is printed | 08 | c csv data | d csv data | e csv data |
| an embedded quote (semicolon); is printed | 09 | c csv data | d csv data | e csv data |
| an embedded escape (asterisk)* is printed | 10 | c csv data | d csv data | e csv data |
| an embedded line feed +| 11 | c csv data | d csv data | e csv data |
| is printed | | | | |
| an embedded single quote ' here | 12 | c csv data | d csv data | e csv data |
| an embedded single quote ' here | 13 | c csv data | d csv data | e csv data |
| an embedded backslash \ here | 14 | c csv data | d csv data | e csv data |
| (14 rows) |
| |
| DROP TABLE copy_regression_csv1; |
| DROP TABLE copy_regression_csv2; |
| DROP TABLE copy_regression_csv3; |
| -- ###################################################### |
| -- FILL MISSING FIELDS |
| -- ###################################################### |
| CREATE TABLE copy_regression_fill1(a int, b int, c text) ; |
| CREATE TABLE copy_regression_fill2(a int, b int, c text) ; |
| -- text |
| COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; |
| COPY copy_regression_fill1(c,b) from stdin with delimiter '|' fill missing fields; |
| COPY copy_regression_fill2(a,c) from stdin with delimiter '|' fill missing fields; |
| SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; |
| a | b | c |
| ---+---+------- |
| 1 | 1 | one |
| 2 | 2 | |
| 3 | | |
| | 1 | one |
| | | three |
| | | two |
| (6 rows) |
| |
| SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; |
| a | b | c |
| ---+---+------- |
| 1 | | one |
| 2 | | |
| 3 | | three |
| (3 rows) |
| |
| TRUNCATE copy_regression_fill1; |
| TRUNCATE copy_regression_fill2; |
| -- csv |
| COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; |
| COPY copy_regression_fill1(c,b) from stdin with csv delimiter '|' fill missing fields; |
| COPY copy_regression_fill2(a,c) from stdin with csv delimiter '|' fill missing fields; |
| SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; |
| a | b | c |
| ---+---+------- |
| 1 | 1 | one |
| 2 | 2 | |
| 3 | | |
| | 1 | one |
| | | three |
| | | two |
| (6 rows) |
| |
| SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; |
| a | b | c |
| ---+---+------- |
| 1 | | one |
| 2 | | |
| 3 | | three |
| (3 rows) |
| |
| -- empty row should fail |
| COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; |
| ERROR: missing data for column "b", found empty data line |
| CONTEXT: COPY copy_regression_fill1, line 1: "" |
| COPY copy_regression_fill2 from stdin with delimiter '|' fill missing fields; |
| ERROR: missing data for column "b", found empty data line |
| CONTEXT: COPY copy_regression_fill2, line 1: "" |
| COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; |
| ERROR: missing data for column "b", found empty data line |
| CONTEXT: COPY copy_regression_fill1, line 1: "" |
| COPY copy_regression_fill2 from stdin with csv delimiter '|' fill missing fields; |
| ERROR: missing data for column "b", found empty data line |
| CONTEXT: COPY copy_regression_fill2, line 1: "" |
| DROP TABLE copy_regression_fill1; |
| DROP TABLE copy_regression_fill2; |
| -- ###################################################### |
| -- FORCE NOT NULL |
| -- ###################################################### |
| CREATE TABLE copy_regression_fnn(a text, b text, c text) ; |
| COPY copy_regression_fnn from stdin with csv; |
| SELECT * FROM copy_regression_fnn WHERE b is null order by a; |
| a | b | c |
| -----+---+----- |
| one | | one |
| two | | |
| (2 rows) |
| |
| SELECT * FROM copy_regression_fnn WHERE c is null order by a; |
| a | b | c |
| -----+---+--- |
| two | | |
| (1 row) |
| |
| TRUNCATE copy_regression_fnn; |
| COPY copy_regression_fnn from stdin with csv force not null b; |
| SELECT * FROM copy_regression_fnn WHERE b is null order by a; |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| SELECT * FROM copy_regression_fnn WHERE c is null order by a; |
| a | b | c |
| -----+---+--- |
| two | | |
| (1 row) |
| |
| TRUNCATE copy_regression_fnn; |
| COPY copy_regression_fnn from stdin with csv force not null b,c; |
| SELECT * FROM copy_regression_fnn WHERE b is null order by a; |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| SELECT * FROM copy_regression_fnn WHERE c is null order by a; |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| TRUNCATE copy_regression_fnn; |
| -- now combine with fill missing fields |
| COPY copy_regression_fnn from stdin with csv fill missing fields force not null b; |
| SELECT * FROM copy_regression_fnn WHERE b is null order by a; |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| SELECT * FROM copy_regression_fnn WHERE c is null order by a; |
| a | b | c |
| -----+---+--- |
| two | | |
| (1 row) |
| |
| DROP TABLE copy_regression_fnn; |
| -- ########################################################### |
| -- distributed data error consolidation + original row numbers |
| -- ########################################################### |
| CREATE TABLE copy_regression_error1(a int, b int) ; |
| -- parse error on QE (extra column on line 6) |
| COPY copy_regression_error1 from stdin; |
| ERROR: extra data after last expected column (seg1 subraa4-mac:40001 pid=80440) |
| CONTEXT: COPY copy_regression_error1, line 6: "6 6 6" |
| -- parse error on QD (missing column on line 3) |
| COPY copy_regression_error1 from stdin; |
| ERROR: missing data for column "b" |
| CONTEXT: COPY copy_regression_error1, line 3: "3" |
| -- convert error on QD (invalid type line 2) |
| COPY copy_regression_error1 from stdin; |
| ERROR: invalid input syntax for type integer: "two" |
| CONTEXT: COPY copy_regression_error1, line 2, column two |
| -- convert error on QE (invalid type line 5) |
| COPY copy_regression_error1 from stdin; |
| ERROR: invalid input syntax for type integer: "five" (seg1 subraa4-mac:40001 pid=80440) |
| CONTEXT: COPY copy_regression_error1, line 5, column 5 |
| DROP TABLE copy_regression_error1; |
| -- ###################################################### |
| -- NEWLINE |
| -- ###################################################### |
| CREATE TABLE copy_regression_newline(a text, b text) ; |
| -- positive: text |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'lf'; |
| -- positive: csv |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'lf' csv; |
| -- negative: text |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'cr'; |
| ERROR: extra data after last expected column |
| CONTEXT: COPY copy_regression_newline, line 1: "1|1 |
| 2|2 |
| " |
| -- negative: csv |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'cr' csv; |
| ERROR: extra data after last expected column |
| CONTEXT: COPY copy_regression_newline, line 1: "1|1 |
| 2|2 |
| \. |
| " |
| -- negative: invalid newline |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'blah'; |
| ERROR: invalid value for NEWLINE "blah" |
| HINT: Valid options are: 'LF', 'CRLF' and 'CR'. |
| -- negative: newline not yet supported for COPY TO |
| COPY copy_regression_newline to stdout with delimiter '|' newline 'blah'; |
| ERROR: newline currently available for data loading only, not unloading |
| DROP TABLE copy_regression_newline; |
| -- Test that FORCE QUOTE option works with the fastpath for integers and |
| -- numerics |
| COPY ( |
| SELECT 123::integer as intcol, 456::numeric as numcol, 'foo' as textcol |
| ) TO stdout CSV FORCE QUOTE intcol, numcol, textcol; |
| "123","456","foo" |
| -- Do the same with a real table, to test that the option also works when |
| -- doing a "dispatched" COPY, i.e. when the COPY output is produced in |
| -- segments |
| CREATE TABLE force_quotes_tbl(intcol integer, numcol numeric, textcol text); |
| INSERT INTO force_quotes_tbl VALUES (123, 456, 'foo'); |
| COPY force_quotes_tbl TO stdout CSV FORCE QUOTE intcol, numcol, textcol; |
| "123","456","foo" |
| DROP TABLE force_quotes_tbl; |
| -- Tests for error log |
| DROP TABLE IF EXISTS errcopy, errcopy_err, errcopy_temp; |
| NOTICE: table "errcopy" does not exist, skipping |
| NOTICE: table "errcopy_err" does not exist, skipping |
| NOTICE: table "errcopy_temp" does not exist, skipping |
| CREATE TABLE errcopy(a int, b int, c text); |
| INSERT INTO errcopy select i, i, case when i <> 5 then i end || '_text' from generate_series(1, 10)i; |
| COPY errcopy to '/tmp/errcopy.csv' csv null ''; |
| -- check if not null constraint not affect error log. |
| TRUNCATE errcopy; |
| ALTER table errcopy ALTER c SET NOT null; |
| COPY errcopy from '/tmp/errcopy.csv' csv null '' log errors segment reject limit 10 rows; |
| ERROR: null value in column "c" of relation "errcopy" violates not-null constraint |
| DETAIL: Failing row contains (5, 5, null). |
| CONTEXT: COPY errcopy, line 7 |
| SELECT * FROM errcopy; |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| -- reject rows with invalid format for int |
| ALTER table errcopy ALTER c DROP NOT null; |
| ALTER table errcopy DROP COLUMN c; |
| ALTER table errcopy ADD COLUMN c int; |
| COPY errcopy from '/tmp/errcopy.csv' csv null '' log errors segment reject limit 10 rows; |
| NOTICE: found 9 data formatting errors (9 or more input rows), rejected related input data |
| SELECT * FROM errcopy; |
| a | b | c |
| ---+---+--- |
| 5 | 5 | |
| (1 row) |
| |
| SELECT relname, errmsg, rawdata FROM gp_read_error_log('errcopy'); |
| relname | errmsg | rawdata |
| ---------+------------------------------------------------------------+--------------- |
| errcopy | invalid input syntax for type integer: "1_text", column c | 1,1,1_text |
| errcopy | invalid input syntax for type integer: "2_text", column c | 2,2,2_text |
| errcopy | invalid input syntax for type integer: "3_text", column c | 3,3,3_text |
| errcopy | invalid input syntax for type integer: "4_text", column c | 4,4,4_text |
| errcopy | invalid input syntax for type integer: "6_text", column c | 6,6,6_text |
| errcopy | invalid input syntax for type integer: "7_text", column c | 7,7,7_text |
| errcopy | invalid input syntax for type integer: "8_text", column c | 8,8,8_text |
| errcopy | invalid input syntax for type integer: "9_text", column c | 9,9,9_text |
| errcopy | invalid input syntax for type integer: "10_text", column c | 10,10,10_text |
| (9 rows) |
| |
| -- reject one row with extra column, one row with fewer columns |
| TRUNCATE errcopy; |
| SELECT gp_truncate_error_log('errcopy'); |
| gp_truncate_error_log |
| ----------------------- |
| t |
| (1 row) |
| |
| COPY (select i::text || ',' || i::text || case when i = 4 then '' else ',' || i::text || case when i = 5 then ',5' else '' end end from generate_series(1, 10)i) to '/tmp/errcopy.csv'; |
| COPY errcopy from '/tmp/errcopy.csv' csv null '' log errors segment reject limit 10 rows; |
| NOTICE: found 2 data formatting errors (2 or more input rows), rejected related input data |
| SELECT * FROM errcopy ORDER BY a; |
| a | b | c |
| ----+----+---- |
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 6 | 6 | 6 |
| 7 | 7 | 7 |
| 8 | 8 | 8 |
| 9 | 9 | 9 |
| 10 | 10 | 10 |
| (8 rows) |
| |
| SELECT relname, errmsg, rawdata FROM gp_read_error_log('errcopy'); |
| relname | errmsg | rawdata |
| ---------+---------------------------------------+--------- |
| errcopy | missing data for column "c" | 4,4 |
| errcopy | extra data after last expected column | 5,5,5,5 |
| (2 rows) |
| |
| -- metacharacter |
| TRUNCATE errcopy; |
| COPY errcopy from stdin csv newline 'LF' log errors segment reject limit 3 rows; |
| NOTICE: found 1 data formatting errors (1 or more input rows), rejected related input data |
| SELECT * FROM errcopy; |
| a | b | c |
| ---+---+--- |
| 1 | 2 | 0 |
| 1 | 3 | 3 |
| (2 rows) |
| |
| -- exceed reject limit |
| TRUNCATE errcopy; |
| SELECT gp_truncate_error_log('errcopy'); |
| gp_truncate_error_log |
| ----------------------- |
| t |
| (1 row) |
| |
| COPY errcopy from stdin delimiter E'\t' log errors segment reject limit 3 rows; |
| ERROR: segment reject limit reached, aborting operation |
| DETAIL: Last error was: invalid input syntax for type integer: "1 4", column a |
| CONTEXT: COPY errcopy, line 3, column a: "1 4" |
| SELECT * FROM errcopy; |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| SELECT relname, filename, bytenum, errmsg FROM gp_read_error_log('errcopy'); |
| relname | filename | bytenum | errmsg |
| ---------+----------+---------+---------------------------------------------------------------------- |
| errcopy | <stdin> | | invalid input syntax for type integer: "1 2 0", column a |
| errcopy | <stdin> | | invalid input syntax for type integer: "1 3 4", column a |
| errcopy | <stdin> | | invalid input syntax for type integer: "1 4", column a |
| (3 rows) |
| |
| -- abort and keep |
| TRUNCATE errcopy; |
| SELECT gp_truncate_error_log('errcopy'); |
| gp_truncate_error_log |
| ----------------------- |
| t |
| (1 row) |
| |
| COPY errcopy from stdin delimiter '/' log errors segment reject limit 3 rows; |
| ERROR: segment reject limit reached, aborting operation |
| DETAIL: Last error was: missing data for column "b" (seg0 subraa4-mac:40000 pid=87505) |
| CONTEXT: COPY errcopy, line 5: "1" |
| SELECT relname, filename, bytenum, errmsg FROM gp_read_error_log('errcopy'); |
| relname | filename | bytenum | errmsg |
| ---------+----------+---------+--------------------------------------- |
| errcopy | <stdin> | | extra data after last expected column |
| errcopy | <stdin> | | missing data for column "b" |
| errcopy | <stdin> | | missing data for column "c" |
| (3 rows) |
| |
| -- |
| -- Test error handling during COPY TO |
| -- |
| -- To trigger an error, we use an output function that throws an |
| -- error on a particular value. The trigger value is 1234. |
| -- |
| CREATE TYPE broken_int4; |
| CREATE FUNCTION broken_int4in(cstring) |
| RETURNS broken_int4 |
| AS 'int4in' |
| LANGUAGE internal IMMUTABLE STRICT; |
| NOTICE: return type broken_int4 is only a shell |
| CREATE FUNCTION broken_int4out(broken_int4) |
| RETURNS cstring |
| AS '@abs_builddir@/regress@DLSUFFIX@', 'broken_int4out' |
| LANGUAGE C IMMUTABLE STRICT; |
| NOTICE: argument type broken_int4 is only a shell |
| CREATE TYPE broken_int4 ( |
| internallength = 4, |
| input = broken_int4in, |
| output = broken_int4out, |
| alignment = int4, |
| passedbyvalue |
| ); |
| -- avoid hitting the error when analyzing the table. |
| set gp_autostats_mode = 'None'; |
| -- Create a table, using the funny datatype, and load it with integers |
| -- 1-10000. The input function works normally, so this works, but if you |
| -- try to select the row with value 1234 from it, the output function will |
| -- throw an error. |
| CREATE TABLE broken_type_test (b broken_int4); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry. |
| insert into broken_type_test select g::text::broken_int4 from generate_series(1, 10000) g; |
| -- Should work, since we don't hit the trigger value 1234. (In principle, |
| -- that's not guaranteed; there's no ORDER BY, so the system could return |
| -- any 10 rows. In practice, this seems pretty safe.) |
| copy (select * from broken_type_test limit 10) to '/tmp/g'; |
| -- Should fail, because we try to output everything, including 1234 |
| copy (select * from broken_type_test) to '/tmp/g'; |
| ERROR: testing failure in output function |
| DETAIL: The trigger value was 1234 |
| copy broken_type_test to '/tmp/g'; |
| ERROR: testing failure in output function |
| DETAIL: The trigger value was 1234 |
| -- Don't leave behind a table that you can't dump. |
| drop table broken_type_test; |
| drop type broken_int4 cascade; -- drops the I/O functions, too. |
| NOTICE: drop cascades to 2 other objects |
| DETAIL: drop cascades to function broken_int4in(cstring) |
| drop cascades to function broken_int4out(broken_int4) |
| -- Test COPY FROM and TO work for catalog tables in dispatch |
| -- connection |
| BEGIN; |
| SET allow_system_table_mods=on; |
| COPY gp_configuration_history from stdin with delimiter '|'; |
| COPY (select dbid from gp_configuration_history where dbid=12345) to stdin; |
| 12345 |
| RESET allow_system_table_mods; |
| -- cannot copy to a catalog table with allow_system_table_mods=off; |
| COPY gp_configuration_history from stdin with delimiter '|'; |
| ERROR: permission denied: "gp_configuration_history" is a system catalog |
| HINT: Make sure the configuration parameter allow_system_table_mods is set. |
| ABORT; |
| -- GPDB makes the database name, and many other things, available |
| -- as environment variables to the program. Test those. |
| -- |
| -- Perform these tests in a funnily named database, to test |
| -- escaping |
| set client_min_messages='warning'; |
| DROP DATABASE IF EXISTS "funny_copy""db'with\\quotes"; |
| reset client_min_messages; |
| CREATE DATABASE "funny_copy""db'with\\quotes"; |
| \c "funny_copy""db'with\\quotes" |
| -- echo will behave differently on different platforms, force to use bash with -E option |
| COPY (SELECT 'data1') TO PROGRAM 'cat > /tmp/gpcopyenvtest; /usr/bin/env bash -c ''echo -E database in COPY TO: $GP_DATABASE >> /tmp/gpcopyenvtest '' ' ESCAPE 'OFF'; |
| CREATE TABLE foo (t text); |
| COPY foo FROM PROGRAM 'cat /tmp/gpcopyenvtest' ESCAPE 'OFF'; |
| COPY foo FROM PROGRAM '/usr/bin/env bash -c ''echo -E database in COPY FROM: $GP_DATABASE''' ESCAPE 'OFF'; |
| select * from foo; |
| t |
| --------------------------------------------------- |
| data1 |
| database in COPY FROM: funny_copy"db'with\\quotes |
| database in COPY TO: funny_copy"db'with\\quotes |
| (3 rows) |
| |
