| -- |
| -- 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) distributed by (b); |
| CREATE TABLE copy_regression_hash2(a text, b text, c text) distributed by (a,c); |
| CREATE TABLE copy_regression_hash3(a varchar, b int, c int, d text) distributed by (a,c,d); |
| CREATE TABLE copy_regression_hash4(a int[], b text) distributed by (a); |
| CREATE TABLE copy_regression_hash5(a text[][]) distributed by (a); |
| CREATE TABLE copy_regression_hash6(a int[], b text[]) distributed by (a,b); |
| CREATE TABLE copy_regression_hash7(a text,b text) distributed randomly; |
| -- 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') distributed by(a); |
| CREATE TABLE copy_regression_default2(a text, b serial, c text) distributed by(a); |
| CREATE TABLE copy_regression_default3(a serial, b text, c text) distributed by(a); |
| -- 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) distributed by(a); |
| -- 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) distributed by(a); |
| CREATE TABLE copy_regression_text2(a varchar ,b varchar, c text, d text, e text) distributed by(a); |
| CREATE TABLE copy_regression_text3(a varchar ,b varchar, c text, d text, e text) distributed by(a); |
| -- 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) distributed by(a); |
| CREATE TABLE copy_regression_csv2(a varchar ,b varchar, c text, d text, e text) distributed by(a); |
| CREATE TABLE copy_regression_csv3(a varchar ,b varchar, c text, d text, e text) distributed by(a); |
| -- 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) distributed by(a); |
| CREATE TABLE copy_regression_fill2(a int, b int, c text) distributed by(c); |
| -- 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) distributed by(a); |
| 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) distributed by(a); |
| -- 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) distributed by(a); |
| -- 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 xxxxx line x: xxx |
| 2|2 |
| " |
| -- negative: csv |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'cr' csv; |
| ERROR: extra data after last expected column |
| CONTEXT: COPY xxxxx line x: xxx |
| 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) DISTRIBUTED BY (intcol); |
| 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) distributed by (a); |
| 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) |
| |
| -- exceed reject limit on segment |
| DROP TABLE IF EXISTS segment_reject_limit; |
| NOTICE: table "segment_reject_limit" does not exist, skipping |
| CREATE TABLE segment_reject_limit (a int,b char) distributed by (a); |
| INSERT INTO segment_reject_limit values(1,'1'); |
| INSERT INTO segment_reject_limit values(1,'2'); |
| INSERT INTO segment_reject_limit values(1,'a'); |
| INSERT INTO segment_reject_limit values(1,'b'); |
| COPY segment_reject_limit TO '/tmp/segment_reject_limit<SEGID>.csv' on segment; |
| DROP TABLE IF EXISTS segment_reject_limit_from; |
| NOTICE: table "segment_reject_limit_from" does not exist, skipping |
| CREATE TABLE segment_reject_limit_from (a int,b int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| COPY segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' on segment log errors segment reject limit 2 rows; |
| ERROR: segment reject limit reached, aborting operation |
| DETAIL: Last error was: invalid input syntax for type integer: "b", column b (seg0 172.17.0.2:40000 pid=8318) |
| CONTEXT: COPY segment_reject_limit_from, line 4, column 1 |
| SELECT * FROM segment_reject_limit_from; |
| a | b |
| ---+--- |
| (0 rows) |
| |
| SELECT relname, filename, bytenum, errmsg FROM gp_read_error_log('segment_reject_limit_from'); |
| relname | filename | bytenum | errmsg |
| ---------------------------+--------------------------------+---------+------------------------------------------------------ |
| segment_reject_limit_from | /tmp/segment_reject_limit1.csv | | invalid input syntax for type integer: "a", column b |
| segment_reject_limit_from | /tmp/segment_reject_limit1.csv | | invalid input syntax for type integer: "b", column b |
| (2 rows) |
| |
| -- not exceed reject limit on segment |
| COPY segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' on segment log errors segment reject limit 3 rows; |
| NOTICE: found 2 data formatting errors (2 or more input rows), rejected related input data |
| SELECT * FROM segment_reject_limit_from; |
| a | b |
| ---+--- |
| 1 | 1 |
| 1 | 2 |
| (2 rows) |
| |
| -- STDIN is not support by copy from on segment and copy program |
| COPY segment_reject_limit_from from STDIN on segment; |
| ERROR: STDIN is not supported by 'COPY ON SEGMENT' |
| COPY segment_reject_limit_from from PROGRAM STDIN; |
| ERROR: STDIN/STDOUT not allowed with PROGRAM |
| LINE 1: COPY segment_reject_limit_from from PROGRAM STDIN; |
| ^ |
| SELECT * FROM segment_reject_limit_from; |
| a | b |
| ---+--- |
| 1 | 1 |
| 1 | 2 |
| (2 rows) |
| |
| -- STDOUT is not support by copy to on segment and copy program |
| COPY segment_reject_limit_from to STDOUT on segment; |
| ERROR: STDOUT is not supported by 'COPY ON SEGMENT' |
| COPY segment_reject_limit_from to PROGRAM STDOUT; |
| ERROR: STDIN/STDOUT not allowed with PROGRAM |
| LINE 1: COPY segment_reject_limit_from to PROGRAM STDOUT; |
| ^ |
| -- 'COPY (SELECT ...) TO' has supported 'ON SEGMENT' |
| COPY (SELECT * FROM segment_reject_limit_from) TO '/tmp/segment_reject_limit<SEGID>.csv' ON SEGMENT; |
| -- 'COPY (SELECT ...) TO' on utility mode |
| CREATE EXTERNAL WEB TABLE copy_cmd_utility(a text, b int) |
| EXECUTE E'PGOPTIONS="-c gp_role=utility" \\ |
| psql -X -p $GP_MASTER_PORT $GP_DATABASE $GP_USER -c \\ |
| "COPY (SELECT * FROM pg_class) TO \'/dev/null\'"' |
| ON COORDINATOR FORMAT 'text' (DELIMITER ' '); |
| SELECT a FROM copy_cmd_utility; |
| a |
| ------ |
| COPY |
| (1 row) |
| |
| DROP EXTERNAL WEB TABLE copy_cmd_utility; |
| -- SREH is not supported by COPY TO. |
| COPY segment_reject_limit_from to STDOUT log errors segment reject limit 3 rows; |
| ERROR: COPY single row error handling only available using COPY FROM |
| -- \copy from doesn't support on segment |
| --on segment lower case |
| \COPY segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' on segment; |
| \COPY command doesn't support ON SEGMENT |
| --on segment with spaces |
| \COPY segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' on segment; |
| \COPY command doesn't support ON SEGMENT |
| --on segment are capital |
| \COPY segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' ON SEGMENT; |
| \COPY command doesn't support ON SEGMENT |
| --on segment with \t |
| \copy segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' on segment; |
| \COPY command doesn't support ON SEGMENT |
| -- \copy to doesn't support on segment |
| --on segment lower case |
| \COPY segment_reject_limit_from to '/tmp/copy_on_segment<SEGID>.csv' on segment; |
| \COPY command doesn't support ON SEGMENT |
| --on segment with spaces |
| \COPY segment_reject_limit_from to '/tmp/copy_on_segment<SEGID>.csv' on segment; |
| \COPY command doesn't support ON SEGMENT |
| --on segment are capital |
| \COPY segment_reject_limit_from to '/tmp/copy_on_segment<SEGID>.csv' ON SEGMENT; |
| \COPY command doesn't support ON SEGMENT |
| --on segment with \t |
| \copy segment_reject_limit_from from '/tmp/copy_on_segment<SEGID>.csv' on segment; |
| \COPY command doesn't support ON SEGMENT |
| -- \copy to/from 'stdin'/'stdout' (quoted) |
| \copy segment_reject_limit_from to 'stdin'; |
| \copy segment_reject_limit_from from 'stdin'; |
| \copy segment_reject_limit_from to 'stdout'; |
| \copy segment_reject_limit_from from 'stdout'; |
| \!rm stdin |
| \!rm stdout |
| -- gp_initial_bad_row_limit guc test. This guc allows user to set the initial |
| -- number of rows which can contain errors before the database stops loading |
| -- the data. If there is a valid row within the first 'n' rows specified by |
| -- this guc, the database continues to load the data. |
| DROP TABLE IF EXISTS test_first_segment_reject_limit; |
| NOTICE: table "test_first_segment_reject_limit" does not exist, skipping |
| CREATE TABLE test_first_segment_reject_limit (a int, b text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| SET gp_initial_bad_row_limit = 2; |
| COPY test_first_segment_reject_limit FROM STDIN WITH DELIMITER '|' segment reject limit 20; |
| ERROR: all 2 first rows in this segment were rejected |
| DETAIL: Aborting operation regardless of REJECT LIMIT value, last error was: invalid input syntax for type integer: "error1", column a |
| CONTEXT: COPY test_first_segment_reject_limit, line 2, column a: "error1" |
| -- should go through fine |
| SET gp_initial_bad_row_limit = 6; |
| COPY test_first_segment_reject_limit FROM STDIN WITH DELIMITER '|' segment reject limit 20; |
| NOTICE: found 4 data formatting errors (4 or more input rows), rejected related input data |
| SELECT COUNT(*) FROM test_first_segment_reject_limit; |
| count |
| ------- |
| 8 |
| (1 row) |
| |
| -- start_ignore |
| -- end_ignore |
| CREATE TABLE test_copy_on_segment_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 test_copy_on_segment_nocol TO '/tmp/valid_filename_nocol<SEGID>.txt' ON SEGMENT; |
| COPY test_copy_on_segment_nocol FROM '/tmp/valid_filename_nocol<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM test_copy_on_segment_nocol; |
| -- |
| (0 rows) |
| |
| COPY (select * from test_copy_on_segment_nocol) TO '/tmp/valid_filename_nocol<SEGID>.txt' ON SEGMENT; |
| COPY test_copy_on_segment_nocol FROM '/tmp/valid_filename_nocol<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM test_copy_on_segment_nocol; |
| -- |
| (0 rows) |
| |
| CREATE TABLE test_copy_on_segment_array (a int[], b text) DISTRIBUTED BY (a); |
| INSERT INTO test_copy_on_segment_array VALUES ('{1,2,3}', 'sd'); |
| INSERT INTO test_copy_on_segment_array VALUES ('{2,2,3}', 'fg'); |
| INSERT INTO test_copy_on_segment_array VALUES ('{3,2,1}', 'hj'); |
| INSERT INTO test_copy_on_segment_array VALUES ('{233,2,1}', 'hj'); |
| INSERT INTO test_copy_on_segment_array VALUES ('{666,2,1}', 'hj'); |
| INSERT INTO test_copy_on_segment_array VALUES ('{3,555,1}', 'hj'); |
| COPY test_copy_on_segment_array TO '/tmp/valid_filename_array<SEGID>.txt' ON SEGMENT; |
| CREATE TABLE test_copy_on_segment_array_1 (a int[], b text) DISTRIBUTED BY (a); |
| COPY test_copy_on_segment_array_1 FROM '/tmp/valid_filename_array<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM test_copy_on_segment_array EXCEPT SELECT * FROM test_copy_on_segment_array_1; |
| a | b |
| ---+--- |
| (0 rows) |
| |
| delete from test_copy_on_segment_array_1; |
| COPY (select * from test_copy_on_segment_array) TO '/tmp/valid_filename_array_select<SEGID>.txt' ON SEGMENT; |
| COPY test_copy_on_segment_array_1 FROM '/tmp/valid_filename_array_select<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM test_copy_on_segment_array EXCEPT SELECT * FROM test_copy_on_segment_array_1; |
| a | b |
| ---+--- |
| (0 rows) |
| |
| CREATE TABLE test_copy_on_segment_2dim_array (a int[][]) DISTRIBUTED BY (a); |
| INSERT INTO test_copy_on_segment_2dim_array VALUES ('{{1,2,3},{2,5,9}}'); |
| INSERT INTO test_copy_on_segment_2dim_array VALUES ('{{1,8,3},{2,5,9}}'); |
| INSERT INTO test_copy_on_segment_2dim_array VALUES ('{{1,9,3},{2,9,9}}'); |
| INSERT INTO test_copy_on_segment_2dim_array VALUES ('{{1,3,3},{2,6,9}}'); |
| INSERT INTO test_copy_on_segment_2dim_array VALUES ('{{1,233,3},{2,5,9}}'); |
| INSERT INTO test_copy_on_segment_2dim_array VALUES ('{{1,666,3},{2,555,9}}'); |
| COPY test_copy_on_segment_2dim_array TO '/tmp/valid_filename_2dim_array<SEGID>.txt' ON SEGMENT; |
| CREATE TABLE test_copy_on_segment_2dim_array_1 (a int[][]) DISTRIBUTED BY (a); |
| COPY test_copy_on_segment_2dim_array_1 FROM '/tmp/valid_filename_2dim_array<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM test_copy_on_segment_2dim_array EXCEPT SELECT * FROM test_copy_on_segment_2dim_array_1; |
| a |
| --- |
| (0 rows) |
| |
| delete from test_copy_on_segment_2dim_array_1; |
| COPY (select * from test_copy_on_segment_2dim_array) TO '/tmp/valid_filename_2dim_array_select<SEGID>.txt' ON SEGMENT; |
| COPY test_copy_on_segment_2dim_array_1 FROM '/tmp/valid_filename_2dim_array_select<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM test_copy_on_segment_2dim_array EXCEPT SELECT * FROM test_copy_on_segment_2dim_array_1; |
| a |
| --- |
| (0 rows) |
| |
| CREATE TABLE test_copy_on_segment (a int, b text, c text) DISTRIBUTED BY (b); |
| INSERT INTO test_copy_on_segment VALUES (1, 's', 'd'); |
| INSERT INTO test_copy_on_segment VALUES (2, 'f', 'g'); |
| INSERT INTO test_copy_on_segment VALUES (3, 'h', 'j'); |
| INSERT INTO test_copy_on_segment VALUES (4, 'i', 'l'); |
| INSERT INTO test_copy_on_segment VALUES (5, 'q', 'w'); |
| COPY test_copy_on_segment TO '/tmp/invalid_filename.txt' ON SEGMENT; |
| ERROR: <SEGID> is required for file name |
| COPY test_copy_on_segment TO '/tmp/valid_filename<SEGID>.txt' ON SEGMENT; |
| COPY test_copy_on_segment TO '/tmp/valid_filename<SEGID>.bin' ON SEGMENT BINARY; |
| COPY test_copy_on_segment TO '/tmp/valid_filename<SEGID>.csv' WITH ON SEGMENT CSV QUOTE '"' FORCE QUOTE a,b,c ESCAPE E'\\' NULL '\N' DELIMITER ',' HEADER IGNORE EXTERNAL PARTITIONS; |
| CREATE TABLE test_copy_from_on_segment_txt (LIKE test_copy_on_segment) DISTRIBUTED BY (b); |
| COPY test_copy_from_on_segment_txt FROM '/tmp/invalid_filename.txt' ON SEGMENT; |
| ERROR: <SEGID> is required for file name |
| COPY test_copy_from_on_segment_txt FROM '/tmp/valid_filename<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM test_copy_from_on_segment_txt ORDER BY a; |
| a | b | c |
| ---+---+--- |
| 1 | s | d |
| 2 | f | g |
| 3 | h | j |
| 4 | i | l |
| 5 | q | w |
| (5 rows) |
| |
| CREATE TABLE test_copy_from_on_segment_binary (LIKE test_copy_on_segment) DISTRIBUTED BY (b); |
| COPY test_copy_from_on_segment_binary FROM '/tmp/valid_filename<SEGID>.bin' ON SEGMENT BINARY; |
| SELECT * FROM test_copy_from_on_segment_binary ORDER BY a; |
| a | b | c |
| ---+---+--- |
| 1 | s | d |
| 2 | f | g |
| 3 | h | j |
| 4 | i | l |
| 5 | q | w |
| (5 rows) |
| |
| CREATE TABLE test_copy_from_on_segment_csv (LIKE test_copy_on_segment) DISTRIBUTED BY (b); |
| COPY test_copy_from_on_segment_csv FROM '/tmp/valid_filename<SEGID>.csv' WITH ON SEGMENT CSV QUOTE '"' ESCAPE E'\\' NULL '\N' DELIMITER ',' HEADER IGNORE EXTERNAL PARTITIONS; |
| SELECT * FROM test_copy_from_on_segment_csv ORDER BY a; |
| a | b | c |
| ---+---+--- |
| 1 | s | d |
| 2 | f | g |
| 3 | h | j |
| 4 | i | l |
| 5 | q | w |
| (5 rows) |
| |
| COPY (select * from test_copy_on_segment) TO '/tmp/invalid_filename_select.txt' ON SEGMENT; |
| ERROR: <SEGID> is required for file name (seg0 127.0.0.1:25432 pid=22593) |
| COPY (select * from test_copy_on_segment) TO '/tmp/valid_filename_select<SEGID>.txt' ON SEGMENT; |
| COPY (select * from test_copy_on_segment) TO '/tmp/valid_filename_select<SEGID>.bin' ON SEGMENT BINARY; |
| COPY test_copy_on_segment TO '/tmp/valid_filename_select<SEGID>.csv' WITH ON SEGMENT CSV QUOTE '"' FORCE QUOTE a,b,c ESCAPE E'\\' NULL '\N' DELIMITER ',' HEADER; |
| delete from test_copy_from_on_segment_txt; |
| COPY test_copy_from_on_segment_txt FROM '/tmp/invalid_filename_select.txt' ON SEGMENT; |
| ERROR: <SEGID> is required for file name (seg0 127.0.0.1:25432 pid=22593) |
| COPY test_copy_from_on_segment_txt FROM '/tmp/valid_filename_select<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM test_copy_from_on_segment_txt ORDER BY a; |
| a | b | c |
| ---+---+--- |
| 1 | s | d |
| 2 | f | g |
| 3 | h | j |
| 4 | i | l |
| 5 | q | w |
| (5 rows) |
| |
| delete from test_copy_from_on_segment_binary; |
| COPY test_copy_from_on_segment_binary FROM '/tmp/valid_filename_select<SEGID>.bin' ON SEGMENT BINARY; |
| SELECT * FROM test_copy_from_on_segment_binary ORDER BY a; |
| a | b | c |
| ---+---+--- |
| 1 | s | d |
| 2 | f | g |
| 3 | h | j |
| 4 | i | l |
| 5 | q | w |
| (5 rows) |
| |
| delete from test_copy_from_on_segment_csv; |
| COPY test_copy_from_on_segment_csv FROM '/tmp/valid_filename_select<SEGID>.csv' WITH ON SEGMENT CSV QUOTE '"' ESCAPE E'\\' NULL '\N' DELIMITER ',' HEADER; |
| SELECT * FROM test_copy_from_on_segment_csv ORDER BY a; |
| a | b | c |
| ---+---+--- |
| 1 | s | d |
| 2 | f | g |
| 3 | h | j |
| 4 | i | l |
| 5 | q | w |
| (5 rows) |
| |
| CREATE TABLE onek_copy_onsegment ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) DISTRIBUTED BY (two,four,odd); |
| \COPY onek_copy_onsegment FROM 'data/onek.data'; |
| SELECT count(*) FROM onek_copy_onsegment; |
| count |
| ------- |
| 1000 |
| (1 row) |
| |
| COPY onek_copy_onsegment TO '/tmp/valid_filename_onek_copy_onsegment<SEGID>.txt' ON SEGMENT; |
| CREATE TABLE onek_copy_from_onsegment (LIKE onek_copy_onsegment); |
| NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table |
| COPY onek_copy_from_onsegment FROM '/tmp/valid_filename_onek_copy_onsegment<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM onek_copy_onsegment EXCEPT SELECT * FROM onek_copy_from_onsegment; |
| unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 |
| ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- |
| (0 rows) |
| |
| SELECT count(*) FROM onek_copy_from_onsegment; |
| count |
| ------- |
| 1000 |
| (1 row) |
| |
| COPY (select * from onek_copy_onsegment) TO '/tmp/valid_filename_onek_copy_onsegment_select<SEGID>.txt' ON SEGMENT; |
| delete from onek_copy_from_onsegment; |
| COPY onek_copy_from_onsegment FROM '/tmp/valid_filename_onek_copy_onsegment_select<SEGID>.txt' ON SEGMENT; |
| SELECT * FROM onek_copy_onsegment EXCEPT SELECT * FROM onek_copy_from_onsegment; |
| unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 |
| ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- |
| (0 rows) |
| |
| SELECT count(*) FROM onek_copy_from_onsegment; |
| count |
| ------- |
| 1000 |
| (1 row) |
| |
| CREATE EXTERNAL WEB TABLE rm_copy_onsegment_files (a int) |
| EXECUTE E'(rm -rf /tmp/*valid_filename*.*)' |
| ON SEGMENT 0 |
| FORMAT 'text'; |
| SELECT * FROM rm_copy_onsegment_files; |
| a |
| --- |
| (0 rows) |
| |
| DROP TABLE IF EXISTS test_copy_on_segment; |
| DROP TABLE IF EXISTS test_copy_on_segment_nocol; |
| DROP TABLE IF EXISTS test_copy_on_segment_array; |
| DROP TABLE IF EXISTS test_copy_on_segment_array_1; |
| DROP TABLE IF EXISTS test_copy_on_segment_2dim_array; |
| DROP TABLE IF EXISTS test_copy_on_segment_2dim_array_1; |
| DROP TABLE IF EXISTS test_copy_from_on_segment_txt; |
| DROP TABLE IF EXISTS test_copy_from_on_segment_binary; |
| DROP TABLE IF EXISTS test_copy_from_on_segment_csv; |
| DROP TABLE IF EXISTS onek_copy_from_onsegment; |
| DROP EXTERNAL TABLE IF EXISTS rm_copy_onsegment_files; |
| -- Test for COPY FROM/TO with mulitple blocks data file |
| -- start_ignore |
| DROP TABLE IF EXISTS LINEITEM; |
| DROP TABLE IF EXISTS LINEITEM_1; |
| DROP TABLE IF EXISTS LINEITEM_2; |
| DROP TABLE IF EXISTS LINEITEM_3; |
| DROP TABLE IF EXISTS LINEITEM_4; |
| DROP TABLE IF EXISTS LINEITEM_5; |
| DROP TABLE IF EXISTS LINEITEM_6; |
| DROP TABLE IF EXISTS LINEITEM_7; |
| DROP TABLE IF EXISTS LINEITEM_8; |
| -- end_ignore |
| CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL, |
| L_PARTKEY INTEGER NOT NULL, |
| L_SUPPKEY INTEGER NOT NULL, |
| L_LINENUMBER INTEGER NOT NULL, |
| L_QUANTITY DECIMAL(15,2) NOT NULL, |
| L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, |
| L_DISCOUNT DECIMAL(15,2) NOT NULL, |
| L_TAX DECIMAL(15,2) NOT NULL, |
| L_RETURNFLAG CHAR(1) NOT NULL, |
| L_LINESTATUS CHAR(1) NOT NULL, |
| L_SHIPDATE DATE NOT NULL, |
| L_COMMITDATE DATE NOT NULL, |
| L_RECEIPTDATE DATE NOT NULL, |
| L_SHIPINSTRUCT CHAR(25) NOT NULL, |
| L_SHIPMODE CHAR(10) NOT NULL, |
| L_COMMENT VARCHAR(44) NOT NULL); |
| -- relation doesn't exist |
| COPY LINEITEM_1 FROM PROGRAM 'yes' CSV; |
| ERROR: relation "lineitem_1" does not exist |
| COPY LINEITEM_1 TO PROGRAM 'yes' CSV; |
| ERROR: relation "lineitem_1" does not exist |
| CREATE TABLE LINEITEM_1 (LIKE LINEITEM); |
| CREATE TABLE LINEITEM_2 (LIKE LINEITEM); |
| CREATE TABLE LINEITEM_3 (LIKE LINEITEM); |
| CREATE TABLE LINEITEM_4 (LIKE LINEITEM); |
| CREATE TABLE LINEITEM_5 (LIKE LINEITEM); |
| CREATE TABLE LINEITEM_6 (LIKE LINEITEM); |
| CREATE TABLE LINEITEM_7 (LIKE LINEITEM); |
| CREATE TABLE LINEITEM_8 (LIKE LINEITEM); |
| COPY LINEITEM FROM '@abs_srcdir@/data/lineitem.csv' WITH DELIMITER '|' CSV; |
| ANALYZE LINEITEM; |
| SELECT COUNT(*) FROM LINEITEM; |
| count |
| ------- |
| 57190 |
| (1 row) |
| |
| COPY LINEITEM TO '/tmp/lineitem.csv' CSV; |
| COPY LINEITEM TO '/tmp/lineitem_s<SEGID>.csv' ON SEGMENT CSV; |
| COPY (select * from LINEITEM) TO '/tmp/lineitem_qs<SEGID>.csv' ON SEGMENT CSV; |
| COPY LINEITEM_1 FROM '/tmp/lineitem.csv' CSV; |
| SELECT COUNT(*) FROM LINEITEM_1; |
| count |
| ------- |
| 57190 |
| (1 row) |
| |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_1; |
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
| ------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+----------------+------------+----------- |
| (0 rows) |
| |
| COPY LINEITEM_2 FROM '/tmp/lineitem_s<SEGID>.csv' ON SEGMENT CSV; |
| SELECT COUNT(*) FROM LINEITEM_2; |
| count |
| ------- |
| 57190 |
| (1 row) |
| |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_2; |
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
| ------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+----------------+------------+----------- |
| (0 rows) |
| |
| COPY LINEITEM_3 FROM '/tmp/lineitem_qs<SEGID>.csv' ON SEGMENT CSV; |
| SELECT COUNT(*) FROM LINEITEM_3; |
| count |
| ------- |
| 57190 |
| (1 row) |
| |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_3; |
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
| ------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+----------------+------------+----------- |
| (0 rows) |
| |
| COPY LINEITEM TO PROGRAM 'cat > /tmp/lineitem_program.csv' CSV; |
| COPY LINEITEM_4 FROM PROGRAM 'cat /tmp/lineitem_program.csv' CSV; |
| SELECT COUNT(*) FROM LINEITEM_4; |
| count |
| ------- |
| 57190 |
| (1 row) |
| |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_4; |
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
| ------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+----------------+------------+----------- |
| (0 rows) |
| |
| COPY (select * from LINEITEM) TO PROGRAM 'cat > /tmp/lineitem_program.csv' CSV; |
| COPY LINEITEM_5 FROM PROGRAM 'cat /tmp/lineitem_program.csv' CSV; |
| SELECT COUNT(*) FROM LINEITEM_5; |
| count |
| ------- |
| 57190 |
| (1 row) |
| |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_5; |
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
| ------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+----------------+------------+----------- |
| (0 rows) |
| |
| COPY LINEITEM TO PROGRAM 'cat > /tmp/lineitem_program<SEGID>.csv' ON SEGMENT CSV; |
| COPY LINEITEM_6 FROM PROGRAM 'cat /tmp/lineitem_program<SEGID>.csv' ON SEGMENT CSV; |
| SELECT COUNT(*) FROM LINEITEM_6; |
| count |
| ------- |
| 57190 |
| (1 row) |
| |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_6; |
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
| ------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+----------------+------------+----------- |
| (0 rows) |
| |
| COPY (select * from LINEITEM) TO PROGRAM 'cat > /tmp/lineitem_program<SEGID>.csv' ON SEGMENT CSV; |
| COPY LINEITEM_7 FROM PROGRAM 'cat /tmp/lineitem_program<SEGID>.csv' ON SEGMENT CSV; |
| SELECT COUNT(*) FROM LINEITEM_7; |
| count |
| ------- |
| 57190 |
| (1 row) |
| |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_7; |
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
| ------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+----------------+------------+----------- |
| (0 rows) |
| |
| \COPY LINEITEM TO PROGRAM 'cat > /tmp/lineitem_program_client.csv' CSV; |
| \COPY LINEITEM_8 FROM PROGRAM 'cat /tmp/lineitem_program_client.csv' CSV; |
| SELECT COUNT(*) FROM LINEITEM_8; |
| count |
| ------- |
| 57190 |
| (1 row) |
| |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_8; |
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
| ------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+----------------+------------+----------- |
| (0 rows) |
| |
| --Test for `COPY FROM ON SEGMENT` checking the distribution key restriction |
| -- start_matchsubs |
| -- m/^CONTEXT: COPY .*, line \d*: .*$/ |
| -- s/^CONTEXT: COPY .*, line \d*: .*$/CONTEXT: COPY xxxxx line x: xxx/ |
| -- end_matchsubs |
| -- start_ignore |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_CHECK_DISTKEY; |
| NOTICE: table "copy_on_segment_check_distkey" does not exist, skipping |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_CHECK_TOW_DSITKEY; |
| NOTICE: table "copy_on_segment_check_tow_dsitkey" does not exist, skipping |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED; |
| NOTICE: table "copy_on_segment_check_distkey_partioned" does not exist, skipping |
| DROP TABLE IF EXISTS COPY_FROM_PARTITION_TABLE; |
| NOTICE: table "copy_from_partition_table" does not exist, skipping |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION; |
| NOTICE: table "copy_on_segment_check_distkey_subpartition" does not exist, skipping |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_DIST_RANDOMLY; |
| NOTICE: table "copy_on_segment_dist_randomly" does not exist, skipping |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_PARTION_DIST_RANDOMLY; |
| NOTICE: table "copy_on_segment_partion_dist_randomly" does not exist, skipping |
| -- end_ignore |
| CREATE TABLE COPY_ON_SEGMENT_CHECK_DISTKEY(a int)DISTRIBUTED BY(a); |
| INSERT INTO COPY_ON_SEGMENT_CHECK_DISTKEY SELECT generate_series(0,10); |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY to '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY<SEGID>.csv' ON SEGMENT CSV; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY to '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY0.csv' CSV; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY FROM '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY<SEGID>.csv' ON SEGMENT CSV; |
| ERROR: value of distribution key doesn't belong to segment with ID 0, it belongs to segment with ID 1 (seg0 127.0.0.1:40000 pid=13100) |
| CONTEXT: COPY copy_on_segment_check_distkey, line 2: "0" |
| SET gp_enable_segment_copy_checking=off; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY FROM '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY<SEGID>.csv' ON SEGMENT CSV; |
| ALTER TABLE COPY_ON_SEGMENT_CHECK_DISTKEY SET DISTRIBUTED RANDOMLY; |
| SET gp_enable_segment_copy_checking=on; |
| CREATE TABLE COPY_ON_SEGMENT_CHECK_TOW_DSITKEY(a int,b int)DISTRIBUTED BY(a,b); |
| INSERT INTO COPY_ON_SEGMENT_CHECK_TOW_DSITKEY SELECT generate_series(0,10),generate_series(0,10); |
| COPY COPY_ON_SEGMENT_CHECK_TOW_DSITKEY to '/tmp/COPY_ON_SEGMENT_CHECK_TOW_DSITKEY<SEGID>.csv' ON SEGMENT CSV; |
| COPY COPY_ON_SEGMENT_CHECK_TOW_DSITKEY to '/tmp/COPY_ON_SEGMENT_CHECK_TOW_DSITKEY0.csv' CSV; |
| COPY COPY_ON_SEGMENT_CHECK_TOW_DSITKEY FROM '/tmp/COPY_ON_SEGMENT_CHECK_TOW_DSITKEY<SEGID>.csv' ON SEGMENT CSV; |
| ERROR: value of distribution key doesn't belong to segment with ID 0, it belongs to segment with ID 1 (seg0 127.0.0.1:40000 pid=13100) |
| CONTEXT: COPY copy_on_segment_check_tow_dsitkey, line 2: "1,1" |
| -- COPY FRON ON SEGMENT DISTRIBUTED RANDOMLY |
| CREATE TABLE COPY_ON_SEGMENT_DIST_RANDOMLY(a int) DISTRIBUTED RANDOMLY; |
| COPY COPY_ON_SEGMENT_DIST_RANDOMLY FROM PROGRAM 'echo <SEGID>' ON SEGMENT; |
| -- COPY FROM ON SEGMENT partitioned table |
| CREATE TABLE COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED(a int) DISTRIBUTED BY (a) PARTITION BY range(a) ( START (0) END (10) EVERY (5)); |
| INSERT INTO COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED SELECT generate_series(0,9); |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED to '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED<SEGID>.csv' ON SEGMENT CSV; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED FROM '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED<SEGID>.csv' ON SEGMENT CSV; |
| SELECT COUNT(*) FROM COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED; |
| count |
| ------- |
| 20 |
| (1 row) |
| |
| -- This adds data to the segment 0's file, that doesn't belong to that |
| -- segment. COPYing that back to the table should throw an error. |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED_1_prt_1 to '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED0.csv' CSV; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED FROM '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED<SEGID>.csv' ON SEGMENT CSV; |
| ERROR: value of distribution key doesn't belong to segment with ID 0, it belongs to segment with ID 1 (seg0 127.0.0.1:40000 pid=13100) |
| CONTEXT: COPY copy_on_segment_check_distkey_partioned, line 1: "0" |
| SELECT COUNT(*) FROM COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED; |
| count |
| ------- |
| 20 |
| (1 row) |
| |
| -- But if we turn off the checks for data distribution, the system will |
| -- merrily load it in anyway. |
| set gp_vmem_idle_resource_timeout=1; |
| SET gp_enable_segment_copy_checking=off; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED FROM '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED<SEGID>.csv' ON SEGMENT CSV; |
| SET gp_enable_segment_copy_checking=on; |
| set gp_vmem_idle_resource_timeout=18000; |
| -- But make sure we still perform the checks for the other partition that is |
| -- hash distributed. |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED_1_prt_2 to '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED0.csv' CSV; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED FROM '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED<SEGID>.csv' ON SEGMENT CSV; |
| ERROR: value of distribution key doesn't belong to segment with ID 0, it belongs to segment with ID 2 (seg0 127.0.0.1:40000 pid=31639) |
| CONTEXT: COPY copy_on_segment_check_distkey_partioned, line 3: "5" |
| -- COPY FROM ON SEGMENT partitioned table distributed randomly |
| CREATE TABLE COPY_ON_SEGMENT_PARTION_DIST_RANDOMLY (a int) DISTRIBUTED RANDOMLY PARTITION BY range(a) ( START (0) END (10) EVERY (5)); |
| COPY COPY_ON_SEGMENT_PARTION_DIST_RANDOMLY FROM PROGRAM 'echo <SEGID>' ON SEGMENT; |
| -- COPY FROM partitioned table |
| TRUNCATE COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED; |
| INSERT INTO COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED SELECT generate_series(0,9); |
| SELECT COUNT(*) FROM COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED; |
| count |
| ------- |
| 10 |
| (1 row) |
| |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED to '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED.csv' CSV; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED FROM '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED.csv' CSV; |
| SELECT COUNT(*) FROM COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED; |
| count |
| ------- |
| 20 |
| (1 row) |
| |
| --COPY FROM partition table mulitple attributes |
| CREATE TABLE COPY_FROM_PARTITION_TABLE (a text ,b int) PARTITION BY range(b) (START (0) END (10) EVERY(5)); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| INSERT INTO COPY_FROM_PARTITION_TABLE values('aa',1); |
| INSERT INTO COPY_FROM_PARTITION_TABLE values('bb',2); |
| INSERT INTO COPY_FROM_PARTITION_TABLE values('cc',8); |
| SELECT COUNT(*) FROM COPY_FROM_PARTITION_TABLE; |
| count |
| ------- |
| 3 |
| (1 row) |
| |
| COPY COPY_FROM_PARTITION_TABLE TO '/tmp/COPY_FROM_PARTITION_TABLE.txt'; |
| COPY COPY_FROM_PARTITION_TABLE FROM '/tmp/COPY_FROM_PARTITION_TABLE.txt'; |
| SELECT COUNT(*) FROM COPY_FROM_PARTITION_TABLE; |
| count |
| ------- |
| 6 |
| (1 row) |
| |
| --COPY FROM ON SEGMENT SUBPARTITION table |
| CREATE TABLE COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION(a int,b int) DISTRIBUTED BY (a) PARTITION BY range(a) |
| SUBPARTITION BY RANGE (b) |
| SUBPARTITION TEMPLATE ( |
| START (0) END (10) EVERY (3) |
| ) |
| ( START (0) END (10) EVERY (5)); |
| INSERT INTO COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION SELECT generate_series(0,9),generate_series(0,9); |
| COPY (SELECT * FROM COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION ORDER BY a, b) to '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION<SEGID>.csv' ON SEGMENT CSV; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION FROM '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION<SEGID>.csv' ON SEGMENT CSV; |
| SELECT COUNT(*) FROM COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION; |
| count |
| ------- |
| 20 |
| (1 row) |
| |
| COPY (VALUES |
| (2, 2), |
| (0, 0) |
| ) to '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION0.csv' CSV; |
| COPY COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION FROM '/tmp/COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION<SEGID>.csv' ON SEGMENT CSV; |
| ERROR: value of distribution key doesn't belong to segment with ID 0, it belongs to segment with ID 1 (seg0 127.0.0.1:40000 pid=13100) |
| CONTEXT: COPY copy_on_segment_check_distkey_subpartition, line 2: "0,0" |
| -- start_ignore |
| DROP TABLE IF EXISTS LINEITEM_1; |
| DROP TABLE IF EXISTS LINEITEM_2; |
| DROP TABLE IF EXISTS LINEITEM_3; |
| DROP TABLE IF EXISTS LINEITEM_4; |
| DROP TABLE IF EXISTS LINEITEM_5; |
| -- end_ignore |
| -- Test for COPY PROGRAM PRINT STDERR |
| -- start_ignore |
| DROP TABLE IF EXISTS COPY_TO_PROGRAM_ERROR; |
| DROP TABLE IF EXISTS COPY_FROM_PROGRAM_ERROR; |
| -- end_ignore |
| CREATE TABLE COPY_TO_PROGRAM_ERROR(dir text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dir' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| COPY COPY_TO_PROGRAM_ERROR TO PROGRAM 'echo && echo "error" >&2 && exit 255'; |
| ERROR: command error message: error |
| COPY COPY_TO_PROGRAM_ERROR TO PROGRAM 'echo <SEGID>&& echo "error" >&2 && exit 255' on segment; |
| ERROR: command error message: error (seg0 127.0.0.1:25432 pid=23338) |
| COPY (SELECT * FROM COPY_TO_PROGRAM_ERROR) TO PROGRAM 'echo <SEGID>&& echo "error" >&2 && exit 255' on segment; |
| ERROR: command error message: error (seg0 127.0.0.1:25432 pid=23338) |
| CREATE TABLE COPY_FROM_PROGRAM_ERROR(a int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'echo 1; echo "error" >&2 && exit 255'; |
| ERROR: command error message: error |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| count |
| ------- |
| 0 |
| (1 row) |
| |
| INSERT INTO COPY_FROM_PROGRAM_ERROR VALUES(0); |
| COPY COPY_FROM_PROGRAM_ERROR TO '/tmp/program_error_<SEGID>.csv' ON SEGMENT; |
| COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'cat /tmp/program_error_<SEGID>.csv && echo "error" >&2 && exit 255' ON SEGMENT; |
| ERROR: command error message: error (seg1 127.0.0.1:40001 pid=23883) |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| -- Test for COPY PROGRAM ENVIROMENT VAR |
| COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'echo $GP_SEGMENT_ID'; |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| \COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'echo $GP_SEGMENT_ID'; |
| ERROR: invalid input syntax for type integer: "" |
| CONTEXT: COPY copy_from_program_error, line 1, column |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'echo $GP_SEGMENT_I'; |
| ERROR: invalid input syntax for type integer: "" |
| CONTEXT: COPY copy_from_program_error, line 1, column |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| SET gp_enable_segment_copy_checking=off; |
| COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'printf "$GP_SEGMENT_ID\n<SEGID>"' on segment; |
| COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'printf "$GP_SEGMENT_I\n<SEGID>"' on segment; |
| ERROR: invalid input syntax for type integer: "" (seg0 172.17.0.2:40000 pid=27935) |
| CONTEXT: COPY copy_from_program_error, line 1, column |
| ALTER TABLE COPY_FROM_PROGRAM_ERROR SET DISTRIBUTED RANDOMLY; |
| SET gp_enable_segment_copy_checking=on; |
| -- Test that xlog records are generated for COPY in the same transaction as |
| -- created table. The actual validation for this test will be performed once the |
| -- gp_replica_check tool is enabled. |
| BEGIN; |
| CREATE TABLE copy_from_same_txn(a int, b int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| COPY copy_from_same_txn FROM stdin; |
| COMMIT; |
| -- Test that COPY can be aborted by cancel request after the COPY is dispatched. |
| SELECT gp_inject_fault('cdb_copy_start_after_dispatch', 'reset', 1); |
| gp_inject_fault |
| ----------------- |
| Success: |
| (1 row) |
| |
| SELECT gp_inject_fault('cdb_copy_start_after_dispatch', 'interrupt', 1); |
| gp_inject_fault |
| ----------------- |
| Success: |
| (1 row) |
| |
| COPY lineitem TO '/tmp/aborted.data'; |
| ERROR: canceling statement due to user request |
| SELECT gp_inject_fault('cdb_copy_start_after_dispatch', 'reset', 1); |
| gp_inject_fault |
| ----------------- |
| Success: |
| (1 row) |
| |
| -- Test external partition |
| CREATE TABLE sales ( |
| id integer, |
| date date, |
| amt numeric(10,2) |
| ) DISTRIBUTED BY (id) PARTITION BY RANGE(date) |
| ( |
| PARTITION jan17 START ('2017-01-01'::date) END ('2017-02-01'::date) WITH (tablename='sales_1_prt_jan17', appendonly=false ), |
| PARTITION feb17 START ('2017-02-01'::date) END ('2017-03-01'::date) WITH (tablename='sales_1_prt_feb17', appendonly=false ), |
| PARTITION mar17 START ('2017-03-01'::date) END ('2017-04-01'::date) WITH (tablename='sales_1_prt_mar17', appendonly=false ), |
| PARTITION apr17 START ('2017-04-01'::date) END ('2017-05-01'::date) WITH (tablename='sales_1_prt_apr17', appendonly=false ), |
| PARTITION may17 START ('2017-05-01'::date) END ('2017-06-01'::date) WITH (tablename='sales_1_prt_may17', appendonly=false ), |
| PARTITION jun17 START ('2017-06-01'::date) END ('2017-07-01'::date) WITH (tablename='sales_1_prt_jun17', appendonly=false ), |
| PARTITION jul17 START ('2017-07-01'::date) END ('2017-08-01'::date) WITH (tablename='sales_1_prt_jul17', appendonly=false ), |
| PARTITION aug17 START ('2017-08-01'::date) END ('2017-09-01'::date) WITH (tablename='sales_1_prt_aug17', appendonly=false ), |
| PARTITION sep17 START ('2017-09-01'::date) END ('2017-10-01'::date) WITH (tablename='sales_1_prt_sep17', appendonly=false ), |
| PARTITION oct17 START ('2017-10-01'::date) END ('2017-11-01'::date) WITH (tablename='sales_1_prt_oct17', appendonly=false ), |
| PARTITION nov17 START ('2017-11-01'::date) END ('2017-12-01'::date) WITH (tablename='sales_1_prt_nov17', appendonly=false ), |
| PARTITION dec17 START ('2017-12-01'::date) END ('2018-01-01'::date) WITH (tablename='sales_1_prt_dec17', appendonly=false ) |
| ); |
| COPY sales (id, date, amt) FROM stdin; |
| -- Set QUIET off, to also test the row counts that COPY returns |
| \set QUIET 'off' |
| COPY sales TO '/tmp/test_sales_<SEGID>' ON SEGMENT; |
| COPY 26 |
| TRUNCATE sales; |
| TRUNCATE TABLE |
| COPY sales FROM '/tmp/test_sales_<SEGID>' ON SEGMENT; |
| COPY 26 |
| CREATE EXTERNAL WEB TABLE ext_dec17(LIKE sales_1_prt_dec17) EXECUTE 'printf "12\t2017-12-01\t21.00\n"' ON COORDINATOR FORMAT 'text'; |
| CREATE EXTERNAL TABLE |
| ALTER TABLE sales EXCHANGE PARTITION dec17 WITH TABLE ext_dec17; |
| NOTICE: partition constraints are not validated when attaching a readable external table |
| ALTER TABLE |
| DROP TABLE ext_dec17; |
| DROP TABLE |
| COPY sales TO PROGRAM 'cat > /tmp/test_sales_all' IGNORE EXTERNAL PARTITIONS; |
| NOTICE: COPY ignores external partition(s) |
| COPY 24 |
| COPY sales TO PROGRAM 'cat > /tmp/test_sales_with_external'; |
| COPY 25 |
| COPY sales TO PROGRAM 'cat > /tmp/test_sales_<SEGID>' ON SEGMENT IGNORE EXTERNAL PARTITIONS; |
| NOTICE: COPY ignores external partition(s) |
| COPY 24 |
| -- Verify that the files don't contain the row from the external partition. |
| \set QUIET 'on' |
| CREATE EXTERNAL WEB TABLE pg_temp.read_result(line text) EXECUTE 'cat /tmp/test_sales_all' ON COORDINATOR FORMAT 'text' (DELIMITER 'off'); |
| SELECT * FROM pg_temp.read_result; |
| line |
| ------------------------------- |
| 1 01-01-2017 20.00 |
| 1 01-01-2017 20.00 |
| 11 11-01-2017 20.00 |
| 11 11-01-2017 20.00 |
| 15 05-01-2017 20.00 |
| 15 05-01-2017 20.00 |
| 18 08-01-2017 20.00 |
| 18 08-01-2017 20.00 |
| 2 02-01-2017 20.00 |
| 2 02-01-2017 20.00 |
| 25 05-01-2017 20.00 |
| 25 05-01-2017 20.00 |
| 3 03-01-2017 20.00 |
| 3 03-01-2017 20.00 |
| 4 04-01-2017 20.00 |
| 4 04-01-2017 20.00 |
| 5 05-01-2017 20.00 |
| 5 05-01-2017 20.00 |
| 7 07-01-2017 20.00 |
| 7 07-01-2017 20.00 |
| 8 08-01-2017 20.00 |
| 8 08-01-2017 20.00 |
| 9 09-01-2017 20.00 |
| 9 09-01-2017 20.00 |
| (24 rows) |
| |
| DROP EXTERNAL TABLE pg_temp.read_result; |
| CREATE EXTERNAL WEB TABLE pg_temp.read_result(line text) EXECUTE 'cat /tmp/test_sales_with_external' ON COORDINATOR FORMAT 'text' (DELIMITER 'off'); |
| SELECT * FROM pg_temp.read_result; |
| line |
| ------------------------------- |
| 1 01-01-2017 20.00 |
| 1 01-01-2017 20.00 |
| 2 02-01-2017 20.00 |
| 2 02-01-2017 20.00 |
| 3 03-01-2017 20.00 |
| 3 03-01-2017 20.00 |
| 4 04-01-2017 20.00 |
| 4 04-01-2017 20.00 |
| 15 05-01-2017 20.00 |
| 15 05-01-2017 20.00 |
| 25 05-01-2017 20.00 |
| 25 05-01-2017 20.00 |
| 5 05-01-2017 20.00 |
| 5 05-01-2017 20.00 |
| 7 07-01-2017 20.00 |
| 7 07-01-2017 20.00 |
| 8 08-01-2017 20.00 |
| 8 08-01-2017 20.00 |
| 18 08-01-2017 20.00 |
| 18 08-01-2017 20.00 |
| 9 09-01-2017 20.00 |
| 9 09-01-2017 20.00 |
| 11 11-01-2017 20.00 |
| 11 11-01-2017 20.00 |
| 12 12-01-2017 21.00 |
| (25 rows) |
| |
| DROP EXTERNAL TABLE pg_temp.read_result; |
| CREATE EXTERNAL WEB TABLE pg_temp.read_result(line text) EXECUTE 'cat /tmp/test_sales_$GP_SEGMENT_ID' FORMAT 'text' (DELIMITER 'off'); |
| SELECT * FROM pg_temp.read_result; |
| line |
| ------------------------------- |
| 1 01-01-2017 20.00 |
| 1 01-01-2017 20.00 |
| 11 11-01-2017 20.00 |
| 11 11-01-2017 20.00 |
| 15 05-01-2017 20.00 |
| 15 05-01-2017 20.00 |
| 18 08-01-2017 20.00 |
| 18 08-01-2017 20.00 |
| 2 02-01-2017 20.00 |
| 2 02-01-2017 20.00 |
| 25 05-01-2017 20.00 |
| 25 05-01-2017 20.00 |
| 3 03-01-2017 20.00 |
| 3 03-01-2017 20.00 |
| 4 04-01-2017 20.00 |
| 4 04-01-2017 20.00 |
| 5 05-01-2017 20.00 |
| 5 05-01-2017 20.00 |
| 7 07-01-2017 20.00 |
| 7 07-01-2017 20.00 |
| 8 08-01-2017 20.00 |
| 8 08-01-2017 20.00 |
| 9 09-01-2017 20.00 |
| 9 09-01-2017 20.00 |
| (24 rows) |
| |
| DROP EXTERNAL TABLE pg_temp.read_result; |
| DROP TABLE sales; |
| CREATE TABLE ao_copy(c int) WITH (appendonly=true); |
| COPY ao_copy TO '/tmp/ao<SEGID>.txt' ON SEGMENT; |
| COPY ao_copy FROM '/tmp/ao<SEGID>.txt' ON SEGMENT; |
| DROP TABLE ao_copy; |
| -- |
| -- Test row counts |
| -- |
| -- We have to disable QUIET mode for these, so that we get the command tags, |
| -- like "COPY 10", in the output. |
| \set QUIET off |
| copy (select generate_series(1, 10)) to '/tmp/a'; |
| COPY 10 |
| create temp table t (id int4); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE TABLE |
| copy t from '/tmp/a'; |
| COPY 10 |
| copy t to '/tmp/b'; |
| COPY 10 |
| copy t to '/tmp/b<SEGID>' on segment; |
| COPY 10 |
| copy t from '/tmp/b<SEGID>' on segment; |
| COPY 10 |
| select count(*) from t; |
| count |
| ------- |
| 20 |
| (1 row) |
| |
| \set QUIET on |
| -- |
| -- 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"; |
| \! python3 test_dbconn.py 1 |
| \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) |
| |
| -- |
| -- Test copy a partitioned table whose columns' orders are not consistent between the root table and partitions. |
| -- |
| -- 1. Test copy a partitioned table with a single distribution key on segment. |
| CREATE TABLE issue_14353 (a integer, b integer, c text) PARTITION BY RANGE (a) DISTRIBUTED BY (a); |
| CREATE TABLE issue_14353_prt_1 (b integer, a integer, c text) DISTRIBUTED BY (a); |
| CREATE TABLE issue_14353_def (a integer, b integer, c text) DISTRIBUTED BY (a); |
| ALTER TABLE ONLY issue_14353 ATTACH PARTITION issue_14353_prt_1 FOR VALUES FROM (1) TO (2); |
| ALTER TABLE ONLY issue_14353 ATTACH PARTITION issue_14353_def DEFAULT; |
| INSERT INTO issue_14353 SELECT x, x%3, 'a' FROM generate_series(1, 20)x; |
| -- Test that the data are successfully copied out and copied in on segment. |
| COPY issue_14353 TO '/tmp/issue_14353_<SEGID>.csv' WITH CSV DELIMITER ',' ON SEGMENT; |
| COPY issue_14353 FROM '/tmp/issue_14353_<SEGID>.csv' WITH CSV DELIMITER ',' ON SEGMENT; |
| -- Test that gpdb emits an error message saying the data distribution is malformed. |
| \! cp /tmp/issue_14353_1.csv /tmp/issue_14353_0.csv |
| COPY issue_14353 FROM '/tmp/issue_14353_<SEGID>.csv' WITH CSV DELIMITER ',' ON SEGMENT; |
| ERROR: value of distribution key doesn't belong to segment with ID 0, it belongs to segment with ID 1 (seg0 127.0.0.1:7002 pid=16166) |
| CONTEXT: COPY issue_14353, line 17: "1,1,a" |
| -- 2. Test copy a partitioned table with multiple distribution keys on segment. |
| CREATE TABLE issue_14353_multi_dist (a integer, b integer, c text) PARTITION BY RANGE (a) DISTRIBUTED BY (a, c); |
| CREATE TABLE issue_14353_multi_dist_prt_1 (b integer, a integer, c text) DISTRIBUTED BY (a, c); |
| CREATE TABLE issue_14353_multi_dist_def (a integer, b integer, c text) DISTRIBUTED BY (a, c); |
| ALTER TABLE ONLY issue_14353_multi_dist ATTACH PARTITION issue_14353__multi_dist_prt_1 FOR VALUES FROM (1) TO (2); |
| ERROR: relation "issue_14353__multi_dist_prt_1" does not exist |
| ALTER TABLE ONLY issue_14353_multi_dist ATTACH PARTITION issue_14353_multi_dist_def DEFAULT; |
| INSERT INTO issue_14353_multi_dist SELECT x, x%3, 'a' || (x%5)::text FROM generate_series(1, 20)x; |
| -- Test that the data are successfully copied out and copied in on segment. |
| COPY issue_14353_multi_dist TO '/tmp/issue_14353_multi_dist_<SEGID>.csv' WITH CSV DELIMITER ',' ON SEGMENT; |
| COPY issue_14353_multi_dist FROM '/tmp/issue_14353_multi_dist_<SEGID>.csv' WITH CSV DELIMITER ',' ON SEGMENT; |
| -- Test that gpdb emits an error message saying the data distribution is malformed. |
| \! cp /tmp/issue_14353_multi_dist_1.csv /tmp/issue_14353_multi_dist_0.csv |
| COPY issue_14353_multi_dist FROM '/tmp/issue_14353_multi_dist_<SEGID>.csv' WITH CSV DELIMITER ',' ON SEGMENT; |
| ERROR: value of distribution key doesn't belong to segment with ID 0, it belongs to segment with ID 1 (seg0 127.0.0.1:7002 pid=21933) |
| CONTEXT: COPY issue_14353_multi_dist, line 11: "1,1,a" |
