| -- |
| -- 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; |
| a data b data c data |
| \. |
| COPY copy_regression_hash1(b,c,a) from stdin; |
| b data c data a data |
| \. |
| SELECT * FROM copy_regression_hash1; |
| |
| -- two keys |
| |
| COPY copy_regression_hash2 from stdin; |
| a data b data c data |
| \. |
| COPY copy_regression_hash2(b,c,a) from stdin; |
| b data c data a data |
| \. |
| SELECT * FROM copy_regression_hash2; |
| |
| -- three keys |
| |
| COPY copy_regression_hash3 from stdin; |
| a data 2 3 d data |
| \. |
| COPY copy_regression_hash3(c,d,b,a) from stdin; |
| 3 d data 2 a data |
| \. |
| COPY copy_regression_hash3(a,c) from stdin; |
| a data 3 |
| \. |
| COPY copy_regression_hash3(d) from stdin; |
| d data |
| \. |
| SELECT * FROM copy_regression_hash3; |
| |
| -- hash on int array |
| |
| COPY copy_regression_hash4 from stdin; |
| {1,2,3,4,5} hashed on an integer array |
| {10,20,30,40,50} hashed on an integer array |
| \. |
| SELECT * FROM copy_regression_hash4 ORDER BY a; |
| |
| -- hash on 2 dim varchar array |
| |
| COPY copy_regression_hash5 from stdin; |
| {{hashing,on},{two dimentional,text array}} |
| \. |
| SELECT * FROM copy_regression_hash5 ORDER BY a; |
| |
| -- hash on int array and varchar array |
| |
| COPY copy_regression_hash6 from stdin; |
| {1,2,3,4,5} {hashing,on,intarray,and,varchararray} |
| \. |
| SELECT * FROM copy_regression_hash6 ORDER BY a; |
| |
| -- hash randomly |
| |
| COPY copy_regression_hash7 from stdin; |
| a data b data |
| a data b data |
| \. |
| SELECT * FROM copy_regression_hash7; |
| |
| 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; |
| a data |
| \. |
| COPY copy_regression_default1(b) from stdin; |
| b data |
| \. |
| SELECT * FROM copy_regression_default1; |
| |
| -- non constant default on non hash key in both text and csv |
| |
| COPY copy_regression_default2(a,c) from stdin; |
| a data c data |
| a data c data |
| a data c data |
| \. |
| COPY copy_regression_default2(a,c) from stdin csv; |
| a data,c data |
| a data,c data |
| a data,c data |
| \. |
| SELECT * FROM copy_regression_default2; |
| |
| -- non constant default on hash key in both text and csv |
| |
| COPY copy_regression_default3(b,c) from stdin; |
| b data c data |
| b data c data |
| b data c data |
| \. |
| COPY copy_regression_default3(b,c) from stdin csv; |
| b data,c data |
| b data,c data |
| b data,c data |
| \. |
| SELECT * FROM copy_regression_default2; |
| |
| 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'; |
| a copyout data line 2 nullval {2,2,2} true |
| \. |
| |
| -- copy out default parameters text format.. |
| COPY copy_regression_out1 to stdout; |
| |
| -- copy out '|' delimiter 'mynull' null.. |
| COPY copy_regression_out1 to stdout with delimiter '|' null 'mynull'; |
| |
| -- copy out '|' delimiter "i'm null" null.. |
| COPY copy_regression_out1 to stdout with delimiter '|' null 'i''m null'; |
| |
| -- copy out default parameters csv format.. |
| COPY copy_regression_out1 to stdout with csv; |
| |
| -- copy out default parameters csv format with header.. |
| COPY copy_regression_out1 to stdout with csv header; |
| |
| -- copy out partial and mixed column list (c,a,b).. |
| COPY copy_regression_out1(c,a,b) to stdout; |
| |
| -- 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 |
| COPY copy_regression_out1 to stdout with csv escape ''''; |
| COPY copy_regression_out1 to stdout with csv quote '''' escape E'\\'; -- make sure E'' is accepted |
| |
| DROP TABLE copy_regression_out1; |
| |
| |
| -- ###################################################### |
| -- Zero column table |
| -- ###################################################### |
| |
| CREATE TABLE copy_regression_nocol(); |
| |
| -- copy in and out of zero column table.. |
| COPY copy_regression_nocol from stdin; |
| \. |
| COPY copy_regression_nocol from stdin; |
| 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 |
| 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; |
| a text data b text data c text data d text data e text data |
| \. |
| COPY copy_regression_text1(a,b,c,d,e) from stdin; |
| a text data b text data c text data d text data e text data |
| \. |
| COPY copy_regression_text1(e,d,c,b,a) from stdin; |
| e text data d text data c text data b text data a text data |
| \. |
| COPY copy_regression_text1(c,a,b,e,d) from stdin; |
| c text data a text data b text data e text data d text data |
| \. |
| COPY copy_regression_text1(a,c) from stdin; |
| a text data c text data |
| \. |
| SELECT * FROM copy_regression_text1; |
| |
| -- null print tests |
| |
| COPY copy_regression_text2 from stdin; |
| a text data \N c text data \N e text data |
| \. |
| COPY copy_regression_text2 from stdin with null 'nullvalue'; |
| a text data nullvalue c text data nullvalue nullvalue |
| \. |
| COPY copy_regression_text2 from stdin with delimiter '|' null ''; |
| a text data||c text data||e text data |
| |b text data|c text data||e text data |
| \. |
| COPY copy_regression_text2 from stdin with delimiter '|' null 'i''m null'; |
| a text data|b text data|i'm null|i'm null|e text data |
| \. |
| SELECT * FROM copy_regression_text2; |
| |
| -- escape tests |
| |
| COPY copy_regression_text3 from stdin with delimiter '|' escape '#'; |
| the at sign: #100 |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 |
| \. |
| COPY copy_regression_text3 from stdin with delimiter '|' escape 'off'; |
| 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 |
| \. |
| COPY copy_regression_text3 from stdin with delimiter '|'; |
| the at sign: \100|6|c text data|d text data|e text data |
| a single backslash \\ in col a|8|c text data|d text data|e text data |
| \. |
| COPY copy_regression_text3 from stdin with delimiter '|'; |
| an embedded linefeed \ |
| and another one\ |
| in column a|7|c text data|d text data|e text data |
| \. |
| COPY copy_regression_text3 from stdin with delimiter '|'; |
| an embedded linefeed sequence\nin column a|7|c text data|d text data|e text data |
| \. |
| |
| SELECT * FROM copy_regression_text3 ORDER BY b,a; |
| |
| 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; |
| a csv data,b csv data,c csv data,d csv data,e csv data |
| \. |
| COPY copy_regression_csv1(a,b,c,d,e) from stdin with csv; |
| a csv data,b csv data,c csv data,d csv data,e csv data |
| \. |
| COPY copy_regression_csv1(e,d,c,b,a) from stdin with csv; |
| e csv data,d csv data,c csv data,b csv data,a csv data |
| \. |
| COPY copy_regression_csv1(c,a,b,e,d) from stdin with csv; |
| c csv data,a csv data,b csv data,e csv data,d csv data |
| \. |
| COPY copy_regression_csv1(a,c) from stdin with csv; |
| a csv data,c csv data |
| \. |
| SELECT * FROM copy_regression_csv1; |
| |
| -- null print tests |
| |
| COPY copy_regression_csv2 from stdin with null E'\\N' csv ; |
| a csv data,\N,c csv data,\N,e csv data |
| \. |
| COPY copy_regression_csv2 from stdin with null 'nullvalue' csv; |
| a csv data,nullvalue,c csv data,nullvalue,nullvalue |
| \. |
| COPY copy_regression_csv2 from stdin with delimiter '|' null '' csv; |
| a csv data||c csv data||e csv data |
| |b csv data|c csv data||e csv data |
| \. |
| SELECT * FROM copy_regression_csv2; |
| |
| -- escape tests |
| |
| COPY copy_regression_csv3 from stdin with csv escape 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 |
| is printed",04,c csv data,d csv data,e csv data |
| \. |
| COPY copy_regression_csv3 from stdin with delimiter E'\t' csv; --default quote and escape - " |
| "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 |
| is printed" 07 c csv data d csv data e csv data |
| \. |
| COPY copy_regression_csv3 from stdin with delimiter '|' csv quote ';' escape '*'; |
| ;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 |
| is printed;|11|c csv data|d csv data|e csv data |
| \. |
| |
| -- check defaults |
| COPY copy_regression_csv3 from stdin with csv quote ''''; -- escape should be the same as quote |
| 'an embedded single quote '' here',12,c csv data,d csv data,e csv data |
| \. |
| COPY copy_regression_csv3 from stdin with csv escape ''''; |
| "an embedded single quote '' here",13,c csv data,d csv data,e csv data |
| \. |
| COPY copy_regression_csv3 from stdin with csv quote '''' escape E'\\'; -- make sure E'' is accepted |
| 'an embedded backslash \\ here',14,c csv data,d csv data,e csv data |
| \. |
| SELECT * FROM copy_regression_csv3 ORDER BY b; |
| |
| 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; |
| 1|1|one |
| 2|2 |
| 3 |
| \. |
| COPY copy_regression_fill1(c,b) from stdin with delimiter '|' fill missing fields; |
| one|1 |
| two |
| three |
| \. |
| COPY copy_regression_fill2(a,c) from stdin with delimiter '|' fill missing fields; |
| 1|one |
| 2 |
| 3|three |
| \. |
| SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; |
| SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; |
| TRUNCATE copy_regression_fill1; |
| TRUNCATE copy_regression_fill2; |
| |
| -- csv |
| COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; |
| 1|1|one |
| 2|2 |
| 3 |
| \. |
| COPY copy_regression_fill1(c,b) from stdin with csv delimiter '|' fill missing fields; |
| one|1 |
| two |
| three |
| \. |
| COPY copy_regression_fill2(a,c) from stdin with csv delimiter '|' fill missing fields; |
| 1|one |
| 2 |
| 3|three |
| \. |
| SELECT * FROM copy_regression_fill1 ORDER BY a,b,c; |
| SELECT * FROM copy_regression_fill2 ORDER BY a,b,c; |
| |
| -- empty row should fail |
| COPY copy_regression_fill1 from stdin with delimiter '|' fill missing fields; |
| |
| \. |
| COPY copy_regression_fill2 from stdin with delimiter '|' fill missing fields; |
| |
| \. |
| COPY copy_regression_fill1 from stdin with csv delimiter '|' fill missing fields; |
| |
| \. |
| COPY copy_regression_fill2 from stdin with csv delimiter '|' fill missing fields; |
| |
| \. |
| 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; |
| one,,one |
| two,, |
| \. |
| SELECT * FROM copy_regression_fnn WHERE b is null order by a; |
| SELECT * FROM copy_regression_fnn WHERE c is null order by a; |
| TRUNCATE copy_regression_fnn; |
| |
| COPY copy_regression_fnn from stdin with csv force not null b; |
| one,,one |
| two,, |
| \. |
| SELECT * FROM copy_regression_fnn WHERE b is null order by a; |
| SELECT * FROM copy_regression_fnn WHERE c is null order by a; |
| TRUNCATE copy_regression_fnn; |
| |
| COPY copy_regression_fnn from stdin with csv force not null b,c; |
| one,,one |
| two,, |
| \. |
| SELECT * FROM copy_regression_fnn WHERE b is null order by a; |
| SELECT * FROM copy_regression_fnn WHERE c is null order by a; |
| 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; |
| one,,one |
| two, |
| \. |
| SELECT * FROM copy_regression_fnn WHERE b is null order by a; |
| SELECT * FROM copy_regression_fnn WHERE c is null order by a; |
| |
| 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; |
| 1 1 |
| 2 2 |
| 3 3 |
| 4 4 |
| 5 5 |
| 6 6 6 |
| 7 7 |
| \. |
| |
| -- parse error on QD (missing column on line 3) |
| COPY copy_regression_error1 from stdin; |
| 1 1 |
| 2 2 |
| 3 |
| 4 4 |
| \. |
| |
| -- convert error on QD (invalid type line 2) |
| |
| COPY copy_regression_error1 from stdin; |
| 1 1 |
| two 2 |
| 3 3 |
| \. |
| |
| -- convert error on QE (invalid type line 5) |
| COPY copy_regression_error1 from stdin; |
| 1 1 |
| 2 2 |
| 3 3 |
| 4 4 |
| 5 five |
| 6 6 |
| 7 7 |
| \. |
| |
| 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'; |
| 1|1 |
| 2|2 |
| \. |
| |
| -- positive: csv |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'lf' csv; |
| 1|1 |
| 2|2 |
| \. |
| |
| -- negative: text |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'cr'; |
| 1|1 |
| 2|2 |
| \. |
| |
| -- negative: csv |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'cr' csv; |
| 1|1 |
| 2|2 |
| \. |
| |
| -- negative: invalid newline |
| COPY copy_regression_newline from stdin with delimiter '|' newline 'blah'; |
| -- negative: newline not yet supported for COPY TO |
| COPY copy_regression_newline to stdout with delimiter '|' newline 'blah'; |
| |
| 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; |
| |
| -- 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; |
| DROP TABLE force_quotes_tbl; |
| |
| -- Tests for error log |
| DROP TABLE IF EXISTS errcopy, errcopy_err, errcopy_temp; |
| |
| 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; |
| |
| SELECT * FROM errcopy; |
| |
| -- 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; |
| SELECT * FROM errcopy; |
| SELECT relname, errmsg, rawdata FROM gp_read_error_log('errcopy'); |
| |
| -- reject one row with extra column, one row with fewer columns |
| TRUNCATE errcopy; |
| SELECT gp_truncate_error_log('errcopy'); |
| |
| 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; |
| |
| SELECT * FROM errcopy ORDER BY a; |
| SELECT relname, errmsg, rawdata FROM gp_read_error_log('errcopy'); |
| |
| -- metacharacter |
| TRUNCATE errcopy; |
| COPY errcopy from stdin csv newline 'LF' log errors segment reject limit 3 rows; |
| 1,2,0 |
| 1,3,4^M |
| 1,3,3 |
| \. |
| |
| SELECT * FROM errcopy; |
| |
| -- exceed reject limit |
| TRUNCATE errcopy; |
| SELECT gp_truncate_error_log('errcopy'); |
| |
| COPY errcopy from stdin delimiter E'\t' log errors segment reject limit 3 rows; |
| 1 2 0 |
| 1 3 4 |
| 1 4 |
| 1 2 |
| 1 |
| 1 3 0 |
| 1 30 999 |
| \. |
| SELECT * FROM errcopy; |
| SELECT relname, filename, bytenum, errmsg FROM gp_read_error_log('errcopy'); |
| |
| -- abort and keep |
| TRUNCATE errcopy; |
| SELECT gp_truncate_error_log('errcopy'); |
| |
| COPY errcopy from stdin delimiter '/' log errors segment reject limit 3 rows; |
| 1/2/3 |
| 1/5 |
| 7/8/9 |
| 1/11/12/ |
| 1 |
| 1/17/18 |
| \. |
| SELECT relname, filename, bytenum, errmsg FROM gp_read_error_log('errcopy'); |
| |
| -- exceed reject limit on segment |
| DROP TABLE IF EXISTS segment_reject_limit; |
| 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; |
| CREATE TABLE segment_reject_limit_from (a int,b int); |
| COPY segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' on segment log errors segment reject limit 2 rows; |
| |
| SELECT * FROM segment_reject_limit_from; |
| SELECT relname, filename, bytenum, errmsg FROM gp_read_error_log('segment_reject_limit_from'); |
| -- 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; |
| SELECT * FROM segment_reject_limit_from; |
| |
| -- STDIN is not support by copy from on segment and copy program |
| COPY segment_reject_limit_from from STDIN on segment; |
| COPY segment_reject_limit_from from PROGRAM STDIN; |
| SELECT * FROM segment_reject_limit_from; |
| |
| -- STDOUT is not support by copy to on segment and copy program |
| COPY segment_reject_limit_from to STDOUT on segment; |
| 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; |
| 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; |
| |
| -- \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; |
| --on segment with spaces |
| \COPY segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' on segment; |
| --on segment are capital |
| \COPY segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' ON SEGMENT; |
| --on segment with \t |
| \copy segment_reject_limit_from from '/tmp/segment_reject_limit<SEGID>.csv' 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; |
| --on segment with spaces |
| \COPY segment_reject_limit_from to '/tmp/copy_on_segment<SEGID>.csv' on segment; |
| --on segment are capital |
| \COPY segment_reject_limit_from to '/tmp/copy_on_segment<SEGID>.csv' ON SEGMENT; |
| --on segment with \t |
| \copy segment_reject_limit_from from '/tmp/copy_on_segment<SEGID>.csv' 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; |
| CREATE TABLE test_first_segment_reject_limit (a int, b text); |
| SET gp_initial_bad_row_limit = 2; |
| COPY test_first_segment_reject_limit FROM STDIN WITH DELIMITER '|' segment reject limit 20; |
| error0 |
| error1 |
| error2 |
| error3 |
| 1|1_number |
| 2|2_number |
| 3|3_number |
| 4|4_number |
| 5|5_number |
| 5|5_number |
| 6|6_number |
| 7|7_number |
| \. |
| |
| -- 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; |
| error0 |
| error1 |
| error2 |
| error3 |
| 1|1_number |
| 2|2_number |
| 3|3_number |
| 4|4_number |
| 5|5_number |
| 5|5_number |
| 6|6_number |
| 7|7_number |
| \. |
| SELECT COUNT(*) FROM test_first_segment_reject_limit; |
| |
| -- start_ignore |
| 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_on_segment_withoids; |
| 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 test_copy_from_on_segment_withoids; |
| DROP TABLE IF EXISTS onek_copy_from_onsegment; |
| DROP EXTERNAL TABLE IF EXISTS rm_copy_onsegment_files; |
| -- end_ignore |
| |
| CREATE TABLE test_copy_on_segment_nocol (); |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| 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; |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| COPY (select * from test_copy_on_segment) TO '/tmp/invalid_filename_select.txt' ON SEGMENT; |
| 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; |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| 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); |
| 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; |
| SELECT count(*) FROM onek_copy_from_onsegment; |
| |
| 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; |
| SELECT count(*) FROM onek_copy_from_onsegment; |
| |
| 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; |
| |
| 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; |
| COPY LINEITEM_1 TO PROGRAM 'yes' CSV; |
| |
| 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); |
| |
| \getenv abs_srcdir PG_ABS_SRCDIR |
| \set lineitem_csv :abs_srcdir '/data/lineitem.csv' |
| COPY LINEITEM FROM :'lineitem_csv' WITH DELIMITER '|' CSV; |
| ANALYZE LINEITEM; |
| SELECT COUNT(*) FROM LINEITEM; |
| 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; |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_1; |
| |
| COPY LINEITEM_2 FROM '/tmp/lineitem_s<SEGID>.csv' ON SEGMENT CSV; |
| SELECT COUNT(*) FROM LINEITEM_2; |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_2; |
| |
| COPY LINEITEM_3 FROM '/tmp/lineitem_qs<SEGID>.csv' ON SEGMENT CSV; |
| SELECT COUNT(*) FROM LINEITEM_3; |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_3; |
| |
| 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; |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_4; |
| |
| 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; |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_5; |
| |
| 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; |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_6; |
| |
| 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; |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_7; |
| |
| \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; |
| SELECT * FROM LINEITEM EXCEPT SELECT * FROM LINEITEM_8; |
| |
| --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; |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_CHECK_TOW_DSITKEY; |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED; |
| DROP TABLE IF EXISTS COPY_FROM_PARTITION_TABLE; |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_CHECK_DISTKEY_SUBPARTITION; |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_DIST_RANDOMLY; |
| DROP TABLE IF EXISTS COPY_ON_SEGMENT_PARTION_DIST_RANDOMLY; |
| -- 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; |
| 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; |
| |
| -- 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; |
| |
| -- 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; |
| SELECT COUNT(*) FROM COPY_ON_SEGMENT_CHECK_DISTKEY_PARTIONED; |
| |
| -- 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; |
| |
| -- 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; |
| 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; |
| |
| --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)); |
| 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; |
| 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; |
| |
| --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; |
| |
| 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; |
| |
| -- 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); |
| |
| COPY COPY_TO_PROGRAM_ERROR TO PROGRAM 'echo && echo "error" >&2 && exit 255'; |
| COPY COPY_TO_PROGRAM_ERROR TO PROGRAM 'echo <SEGID>&& echo "error" >&2 && exit 255' on segment; |
| COPY (SELECT * FROM COPY_TO_PROGRAM_ERROR) TO PROGRAM 'echo <SEGID>&& echo "error" >&2 && exit 255' on segment; |
| |
| CREATE TABLE COPY_FROM_PROGRAM_ERROR(a int); |
| |
| COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'echo 1; echo "error" >&2 && exit 255'; |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| 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; |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| |
| -- Test for COPY PROGRAM ENVIROMENT VAR |
| COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'echo $GP_SEGMENT_ID'; |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| \COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'echo $GP_SEGMENT_ID'; |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| COPY COPY_FROM_PROGRAM_ERROR FROM PROGRAM 'echo $GP_SEGMENT_I'; |
| SELECT COUNT(*) FROM COPY_FROM_PROGRAM_ERROR; |
| 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; |
| 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); |
| COPY copy_from_same_txn FROM stdin; |
| 1 1 |
| 2 2 |
| 3 3 |
| \. |
| 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); |
| SELECT gp_inject_fault('cdb_copy_start_after_dispatch', 'interrupt', 1); |
| |
| COPY lineitem TO '/tmp/aborted.data'; |
| |
| SELECT gp_inject_fault('cdb_copy_start_after_dispatch', 'reset', 1); |
| |
| |
| -- 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; |
| 1 2017-01-01 20.00 |
| 3 2017-03-01 20.00 |
| 25 2017-05-01 20.00 |
| 1 2017-01-01 20.00 |
| 3 2017-03-01 20.00 |
| 25 2017-05-01 20.00 |
| 2 2017-02-01 20.00 |
| 4 2017-04-01 20.00 |
| 8 2017-08-01 20.00 |
| 2 2017-02-01 20.00 |
| 4 2017-04-01 20.00 |
| 8 2017-08-01 20.00 |
| 15 2017-05-01 20.00 |
| 5 2017-05-01 20.00 |
| 9 2017-09-01 20.00 |
| 15 2017-05-01 20.00 |
| 5 2017-05-01 20.00 |
| 9 2017-09-01 20.00 |
| 7 2017-07-01 20.00 |
| 11 2017-11-01 20.00 |
| 7 2017-07-01 20.00 |
| 11 2017-11-01 20.00 |
| 18 2017-08-01 20.00 |
| 12 2017-12-01 20.00 |
| 18 2017-08-01 20.00 |
| 12 2017-12-01 20.00 |
| \. |
| -- Set QUIET off, to also test the row counts that COPY returns |
| \set QUIET 'off' |
| |
| COPY sales TO '/tmp/test_sales_<SEGID>' ON SEGMENT; |
| TRUNCATE sales; |
| COPY sales FROM '/tmp/test_sales_<SEGID>' ON SEGMENT; |
| |
| CREATE EXTERNAL WEB TABLE ext_dec17(LIKE sales_1_prt_dec17) EXECUTE 'printf "12\t2017-12-01\t21.00\n"' ON COORDINATOR FORMAT 'text'; |
| ALTER TABLE sales EXCHANGE PARTITION dec17 WITH TABLE ext_dec17; |
| DROP TABLE ext_dec17; |
| COPY sales TO PROGRAM 'cat > /tmp/test_sales_all' IGNORE EXTERNAL PARTITIONS; |
| COPY sales TO PROGRAM 'cat > /tmp/test_sales_with_external'; |
| COPY sales TO PROGRAM 'cat > /tmp/test_sales_<SEGID>' ON SEGMENT IGNORE EXTERNAL PARTITIONS; |
| |
| -- 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; |
| 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; |
| 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; |
| 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'; |
| |
| create temp table t (id int4); |
| copy t from '/tmp/a'; |
| copy t to '/tmp/b'; |
| copy t to '/tmp/b<SEGID>' on segment; |
| copy t from '/tmp/b<SEGID>' on segment; |
| select count(*) from t; |
| |
| \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; |
| \getenv abs_builddir PG_ABS_BUILDDIR |
| \set regress_dll :abs_builddir '/regress.so' |
| |
| CREATE FUNCTION broken_int4out(broken_int4) |
| RETURNS cstring |
| AS :'regress_dll', 'broken_int4out' |
| LANGUAGE C IMMUTABLE STRICT; |
| |
| 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); |
| 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'; |
| copy broken_type_test to '/tmp/g'; |
| |
| -- 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. |
| |
| -- 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 '|'; |
| 1900-01-01 00:00:00.000000-07|12345|Just testing COPY |
| \. |
| |
| COPY (select dbid from gp_configuration_history where dbid=12345) to stdin; |
| 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 '|'; |
| 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; |
| |
| -- |
| -- 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; |
| |
| -- 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); |
| 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; |
