http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/66f0bda1/src/test/regress/expected/goh_partition.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/goh_partition.out b/src/test/regress/expected/goh_partition.out deleted file mode 100755 index b26a76d..0000000 --- a/src/test/regress/expected/goh_partition.out +++ /dev/null @@ -1,2325 +0,0 @@ -set enable_partition_rules = false; -set gp_enable_hash_partitioned_tables = true; --- missing subpartition by -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by hash (b) -( -partition aa (subpartition cc, subpartition dd), -partition bb (subpartition cc, subpartition dd) -); -ERROR: missing SUBPARTITION BY clause for subpartition specification -LINE 5: partition aa (subpartition cc, subpartition dd), - ^ --- missing subpartition spec -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by hash (b) -subpartition by hash (d) -( -partition aa , -partition bb -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -ERROR: hash partition requires PARTITIONS clause or partition specification -LINE 4: subpartition by hash (d) - ^ --- subpart spec conflict -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by hash (b) -subpartition by hash (d) subpartition template (subpartition jjj) -( -partition aa (subpartition cc, subpartition dd), -partition bb (subpartition cc, subpartition dd) -); -ERROR: subpartition configuration conflicts with subpartition template -LINE 4: subpartition by hash (d) subpartition template (subpartition... - ^ --- missing subpartition by -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by hash (b) -subpartition by hash (d) -( -partition aa (subpartition cc, subpartition dd (subpartition iii)), -partition bb (subpartition cc, subpartition dd) -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc" for table "ggg_1_prt_aa" -ERROR: missing SUBPARTITION BY clause for subpartition specification (at depth 2) -LINE 6: partition aa (subpartition cc, subpartition dd (subpartition... - ^ --- Test column lookup works -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by hash(doesnotexist) -partitions 3; -ERROR: column "doesnotexist" does not exist in relation "ggg" -LINE 3: partition by hash(doesnotexist) - ^ -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by hash(b) -partitions 3 -subpartition by list(alsodoesntexist) -subpartition template ( -subpartition aa values(1) -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_3" for table "ggg" -ERROR: column "alsodoesntexist" does not exist in relation "ggg_1_prt_1" -LINE 5: subpartition by list(alsodoesntexist) - ^ --- will not work since sql has no subpartition templates -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by hash (b) -subpartition by hash (d) -( -partition aa (subpartition cc, subpartition dd), -partition bb (subpartition cc, subpartition dd) -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd" for table "ggg_1_prt_bb" -drop table if exists ggg cascade; --- disable hash partitions -set gp_enable_hash_partitioned_tables = false; -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by hash (b) -subpartition by hash (d) -( -partition aa (subpartition cc, subpartition dd), -partition bb (subpartition cc, subpartition dd) -); -ERROR: PARTITION BY must specify RANGE or LIST -drop table ggg cascade; -ERROR: table "ggg" does not exist -set gp_enable_hash_partitioned_tables = true; --- should work -create table ggg (a char(1), b char(2), d char(3), e int) -distributed by (a) -partition by hash (b) -subpartition by hash (d) -subpartition template ( -subpartition cc, -subpartition dd -), -subpartition by hash (e) -subpartition template ( -subpartition ee, -subpartition ff -) -( -partition aa, -partition bb -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ee" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ff" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ee" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ff" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc_3_prt_ee" for table "ggg_1_prt_bb_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc_3_prt_ff" for table "ggg_1_prt_bb_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_ee" for table "ggg_1_prt_bb_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_ff" for table "ggg_1_prt_bb_2_prt_dd" -drop table ggg cascade; --- should not work since the first-level subpartitioning has no template -create table ggg (a char(1), b char(2), d char(3), e int) -distributed by (a) -partition by hash (b) -subpartition by hash (d), -subpartition by hash (e) -subpartition template ( -subpartition ee, -subpartition ff -) -( -partition aa (subpartition cc, subpartition dd), -partition bb (subpartition cc, subpartition dd) -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ee" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ff" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ee" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ff" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc_3_prt_ee" for table "ggg_1_prt_bb_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc_3_prt_ff" for table "ggg_1_prt_bb_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_ee" for table "ggg_1_prt_bb_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_ff" for table "ggg_1_prt_bb_2_prt_dd" -drop table if exists ggg cascade; --- doesn't work because cannot have nested declaration in template -create table ggg (a char(1), b char(2), d char(3), e int) -distributed by (a) -partition by hash (b) -subpartition by hash (d) -subpartition template ( -subpartition cc (subpartition ee, subpartition ff), -subpartition dd (subpartition ee, subpartition ff) -), -subpartition by hash (e) -( -partition aa, -partition bb -); -ERROR: template cannot contain specification for child partition -drop table ggg cascade; -ERROR: table "ggg" does not exist ---ERROR: Missing boundary specification in partition 'aa' of type LIST -create table fff (a char(1), b char(2), d char(3)) distributed by -(a) partition by list (b) (partition aa ); -ERROR: missing boundary specification in partition "aa" of type LIST -LINE 2: (a) partition by list (b) (partition aa ); - ^ --- ERROR: Invalid use of RANGE boundary specification in partition --- number 1 of type LIST -create table fff (a char(1), b char(2), d char(3)) distributed by (a) -partition by list (b) (start ('a') ); -ERROR: invalid boundary specification for LIST partition -LINE 2: partition by list (b) (start ('a') ); - ^ --- should work -create table fff (a char(1), b char(2), d char(3)) distributed by (a) -partition by list (b) (partition aa values ('2')); -NOTICE: CREATE TABLE will create partition "fff_1_prt_aa" for table "fff" -drop table fff cascade; --- Invalid use of RANGE boundary specification in partition "cc" of --- type HASH (at depth 2) & subpartition at one level has no template -create table ggg (a char(1), b char(2), d char(3), e int) distributed by (a) -partition by hash (b) subpartition by hash (d), -subpartition by hash (e) -subpartition template ( subpartition ee, subpartition ff ) ( -partition aa (subpartition cc, subpartition dd), partition bb -(subpartition cc start ('a') , subpartition dd) ); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ee" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ff" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ee" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ff" for table "ggg_1_prt_aa_2_prt_dd" -ERROR: invalid use of RANGE boundary specification in partition "cc" of type HASH (at depth 2) -LINE 6: (subpartition cc start ('a') , subpartition dd) ); - ^ --- this is subtly wrong -- it defines 4 partitions --- the problem is the comma before "end", which causes us to --- generate 2 anonymous partitions. --- This is an error: --- ERROR: invalid use of mixed named and unnamed RANGE boundary specifications -create table ggg (a char(1), b int, d char(3)) -distributed by (a) -partition by range (b) -( -partition aa start ('2007'), end ('2008'), -partition bb start ('2008'), end ('2009') -); -ERROR: invalid use of mixed named and unnamed RANGE boundary specifications -LINE 5: partition aa start ('2007'), end ('2008'), - ^ -create table ggg (a char(1), b int) -distributed by (a) -partition by range(b) -( -partition aa start ('2007'), end ('2008') -); -ERROR: invalid use of mixed named and unnamed RANGE boundary specifications -LINE 5: partition aa start ('2007'), end ('2008') - ^ -drop table ggg cascade; -ERROR: table "ggg" does not exist -create table ggg (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition aa start (date '2007-01-01') end (date '2008-01-01'), -partition bb start (date '2008-01-01') end (date '2009-01-01') -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -drop table ggg cascade; --- don't allow nonconstant expressions, even simple ones... -create table ggg (a char(1), b numeric, d numeric) -distributed by (a) -partition by range (b,d) -( -partition aa start (2007,1) end (2008,2+2), -partition bb start (2008,2) end (2009,3) -); -ERROR: syntax error at or near "+" -LINE 5: partition aa start (2007,1) end (2008,2+2), - ^ --- composite key -create table ggg (a char(1), b numeric, d numeric) -distributed by (a) -partition by range (b,d) -( -partition aa start (2007,1) end (2008,2), -partition bb start (2008,2) end (2009,3) -); -ERROR: Composite partition keys are not allowed --- demo starts here --- nested subpartitions -create table ggg - (a char(1), b date, - d char(3), e numeric, - f numeric, g numeric, - h numeric) -distributed by (a) -partition by hash(b) -partitions 2 -subpartition by hash(d) -subpartitions 2, -subpartition by hash(e) subpartitions 2, -subpartition by hash(f) subpartitions 2, -subpartition by hash(g) subpartitions 2, -subpartition by hash(h) subpartitions 2; -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1" for table "ggg_1_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2" for table "ggg_1_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1" for table "ggg_1_prt_1_2_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2" for table "ggg_1_prt_1_2_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_2_6_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_2_6_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_1_6_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_1_6_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_2_6_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_2_6_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_1_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_1_6_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_1_6_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_2_6_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_2_6_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_1_6_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_1_6_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_2_6_prt_1" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_2_6_prt_2" for table "ggg_1_prt_1_2_prt_1_3_prt_2_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1" for table "ggg_1_prt_1_2_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2" for table "ggg_1_prt_1_2_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_1_6_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_1_6_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_2_6_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_2_6_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_1_6_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_1_6_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_2_6_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_2_6_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_1_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_1_6_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_1_6_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_2_6_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_2_6_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_1_6_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_1_6_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_2_6_prt_1" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_2_6_prt_2" for table "ggg_1_prt_1_2_prt_2_3_prt_2_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1" for table "ggg_1_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2" for table "ggg_1_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1" for table "ggg_1_prt_2_2_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2" for table "ggg_1_prt_2_2_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_1_6_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_2_6_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_2_6_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_1_6_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_1_6_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_2_6_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_2_6_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_1_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_1_6_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_1_6_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_2_6_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_2_6_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_1_6_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_1_6_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_2_6_prt_1" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_2_6_prt_2" for table "ggg_1_prt_2_2_prt_1_3_prt_2_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1" for table "ggg_1_prt_2_2_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2" for table "ggg_1_prt_2_2_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_1_6_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_1_6_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_2_6_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_2_6_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_1_6_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_1_6_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_2_6_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_2_6_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_1_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_1_6_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_1_6_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_2_6_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_2_6_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_1_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_1_6_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_1_6_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_1" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_2_6_prt_1" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_2" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_2_6_prt_2" for table "ggg_1_prt_2_2_prt_2_3_prt_2_4_prt_2_5_prt_2" -drop table ggg cascade; --- named, inline subpartitions -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by hash (b) -subpartition by hash (d) -( -partition aa (subpartition cc, subpartition dd), -partition bb (subpartition cc, subpartition dd) -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd" for table "ggg_1_prt_bb" -drop table ggg cascade; --- subpartitions with templates -create table ggg (a char(1), b char(2), d char(3), e numeric) -distributed by (a) -partition by hash (b) -subpartition by hash (d) -subpartition template ( -subpartition cc, -subpartition dd -), -subpartition by hash (e) -subpartition template ( -subpartition ee, -subpartition ff -) -( -partition aa, -partition bb -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ee" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ff" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ee" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ff" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc_3_prt_ee" for table "ggg_1_prt_bb_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc_3_prt_ff" for table "ggg_1_prt_bb_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_ee" for table "ggg_1_prt_bb_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_ff" for table "ggg_1_prt_bb_2_prt_dd" -drop table ggg cascade; --- mixed inline subpartition declarations with templates -create table ggg (a char(1), b char(2), d char(3), e numeric) -distributed by (a) -partition by hash (b) -subpartition by hash (d) , -subpartition by hash (e) -subpartition template ( -subpartition ee, -subpartition ff -) -( -partition aa (subpartition cc, subpartition dd), -partition bb (subpartition cc, subpartition dd) -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ee" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_cc_3_prt_ff" for table "ggg_1_prt_aa_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ee" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_ff" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc_3_prt_ee" for table "ggg_1_prt_bb_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_cc_3_prt_ff" for table "ggg_1_prt_bb_2_prt_cc" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_ee" for table "ggg_1_prt_bb_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_ff" for table "ggg_1_prt_bb_2_prt_dd" -drop table ggg cascade; --- basic list partition -create table ggg (a char(1), b char(2), d char(3)) -distributed by (a) -partition by LIST (b) -( -partition aa values ('a', 'b', 'c', 'd'), -partition bb values ('e', 'f', 'g') -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -insert into ggg values ('x', 'a'); -insert into ggg values ('x', 'b'); -insert into ggg values ('x', 'c'); -insert into ggg values ('x', 'd'); -insert into ggg values ('x', 'e'); -insert into ggg values ('x', 'f'); -insert into ggg values ('x', 'g'); -insert into ggg values ('x', 'a'); -insert into ggg values ('x', 'b'); -insert into ggg values ('x', 'c'); -insert into ggg values ('x', 'd'); -insert into ggg values ('x', 'e'); -insert into ggg values ('x', 'f'); -insert into ggg values ('x', 'g'); -select * from ggg order by 1, 2; - a | b | d ----+----+--- - x | a | - x | a | - x | b | - x | b | - x | c | - x | c | - x | d | - x | d | - x | e | - x | e | - x | f | - x | f | - x | g | - x | g | -(14 rows) - --- ok -select * from ggg_1_prt_aa order by 1, 2; - a | b | d ----+----+--- - x | a | - x | a | - x | b | - x | b | - x | c | - x | c | - x | d | - x | d | -(8 rows) - -select * from ggg_1_prt_bb order by 1, 2; - a | b | d ----+----+--- - x | e | - x | e | - x | f | - x | f | - x | g | - x | g | -(6 rows) - -drop table ggg cascade; --- documentation example - partition by list and range -CREATE TABLE rank (id int, rank int, year date, gender -char(1)) DISTRIBUTED BY (id, gender, year) -partition by list (gender) -subpartition by range (year) -subpartition template ( -start (date '2001-01-01'), -start (date '2002-01-01'), -start (date '2003-01-01'), -start (date '2004-01-01'), -start (date '2005-01-01') -) -( - partition boys values ('M'), - partition girls values ('F') -); -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys" for table "rank" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls" for table "rank" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_1" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_2" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_3" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_4" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_5" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_1" for table "rank_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_2" for table "rank_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_3" for table "rank_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_4" for table "rank_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_5" for table "rank_1_prt_girls" -insert into rank values (1, 1, date '2001-01-15', 'M'); -insert into rank values (2, 1, date '2002-02-15', 'M'); -insert into rank values (3, 1, date '2003-03-15', 'M'); -insert into rank values (4, 1, date '2004-04-15', 'M'); -insert into rank values (5, 1, date '2005-05-15', 'M'); -insert into rank values (6, 1, date '2001-01-15', 'F'); -insert into rank values (7, 1, date '2002-02-15', 'F'); -insert into rank values (8, 1, date '2003-03-15', 'F'); -insert into rank values (9, 1, date '2004-04-15', 'F'); -insert into rank values (10, 1, date '2005-05-15', 'F'); -select * from rank order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 1 | 1 | 01-15-2001 | M - 2 | 1 | 02-15-2002 | M - 3 | 1 | 03-15-2003 | M - 4 | 1 | 04-15-2004 | M - 5 | 1 | 05-15-2005 | M - 6 | 1 | 01-15-2001 | F - 7 | 1 | 02-15-2002 | F - 8 | 1 | 03-15-2003 | F - 9 | 1 | 04-15-2004 | F - 10 | 1 | 05-15-2005 | F -(10 rows) - -select * from rank_1_prt_boys order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 1 | 1 | 01-15-2001 | M - 2 | 1 | 02-15-2002 | M - 3 | 1 | 03-15-2003 | M - 4 | 1 | 04-15-2004 | M - 5 | 1 | 05-15-2005 | M -(5 rows) - -select * from rank_1_prt_girls order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 6 | 1 | 01-15-2001 | F - 7 | 1 | 02-15-2002 | F - 8 | 1 | 03-15-2003 | F - 9 | 1 | 04-15-2004 | F - 10 | 1 | 05-15-2005 | F -(5 rows) - -select * from rank_1_prt_girls_2_prt_1 order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 6 | 1 | 01-15-2001 | F -(1 row) - -select * from rank_1_prt_girls_2_prt_2 order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 7 | 1 | 02-15-2002 | F -(1 row) - -drop table rank cascade; --- range list hash combo, but subpartitions has no templates -create table ggg (a char(1), b date, d char(3), e numeric) -distributed by (a) -partition by range (b) -subpartition by list(d), -subpartition by hash(e) subpartitions 3 -( -partition aa -start (date '2007-01-01') -end (date '2008-01-01') - (subpartition dd values ('1', '2', '3'), - subpartition ee values ('4', '5', '6')), -partition bb -start (date '2008-01-01') -end (date '2009-01-01') - (subpartition dd values ('1', '2', '3'), - subpartition ee values ('4', '5', '6')) -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_ee" for table "ggg_1_prt_aa" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_1" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_2" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_dd_3_prt_3" for table "ggg_1_prt_aa_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_ee_3_prt_1" for table "ggg_1_prt_aa_2_prt_ee" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_ee_3_prt_2" for table "ggg_1_prt_aa_2_prt_ee" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa_2_prt_ee_3_prt_3" for table "ggg_1_prt_aa_2_prt_ee" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_ee" for table "ggg_1_prt_bb" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_1" for table "ggg_1_prt_bb_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_2" for table "ggg_1_prt_bb_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_dd_3_prt_3" for table "ggg_1_prt_bb_2_prt_dd" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_ee_3_prt_1" for table "ggg_1_prt_bb_2_prt_ee" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_ee_3_prt_2" for table "ggg_1_prt_bb_2_prt_ee" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2_prt_ee_3_prt_3" for table "ggg_1_prt_bb_2_prt_ee" -drop table ggg cascade; --- demo ends here --- LIST validation --- duplicate partition name -CREATE TABLE rank (id int, rank int, year date, gender -char(1)) DISTRIBUTED BY (id, gender, year) -partition by list (gender) -( - partition boys values ('M'), - partition girls values ('a'), - partition girls values ('b'), - partition girls values ('c'), - partition girls values ('d'), - partition girls values ('e'), - partition bob values ('M') -); -ERROR: duplicate partition name for partition "girls" -LINE 7: partition girls values ('b'), - ^ --- RANGE validation --- legal if end of aa not inclusive -create table ggg (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition aa start (date '2007-01-01') end (date '2008-01-01'), -partition bb start (date '2008-01-01') end (date '2009-01-01') -every (interval '10 days')); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_1" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_3" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_4" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_5" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_6" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_7" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_8" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_9" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_10" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_11" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_12" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_13" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_14" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_15" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_16" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_17" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_18" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_19" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_20" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_21" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_22" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_23" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_24" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_25" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_26" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_27" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_28" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_29" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_30" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_31" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_32" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_33" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_34" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_35" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_36" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_37" for table "ggg" -drop table ggg cascade; --- bad - legal if end of aa not inclusive -create table ggg (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive, -partition bb start (date '2008-01-01') end (date '2009-01-01') -every (interval '10 days')); -ERROR: starting value of partition "bb_1" overlaps previous range -LINE 6: partition bb start (date '2008-01-01') end (date '2009-01-01... - ^ -drop table ggg cascade; -ERROR: table "ggg" does not exist --- legal because start of bb not inclusive -create table ggg (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive, -partition bb start (date '2008-01-01') exclusive end (date '2009-01-01') -every (interval '10 days')); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_1" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_2" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_3" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_4" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_5" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_6" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_7" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_8" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_9" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_10" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_11" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_12" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_13" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_14" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_15" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_16" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_17" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_18" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_19" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_20" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_21" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_22" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_23" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_24" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_25" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_26" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_27" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_28" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_29" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_30" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_31" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_32" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_33" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_34" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_35" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_36" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb_37" for table "ggg" -drop table ggg cascade; --- legal if end of aa not inclusive -create table ggg (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition bb start (date '2008-01-01') end (date '2009-01-01'), -partition aa start (date '2007-01-01') end (date '2008-01-01') -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -drop table ggg cascade; --- bad - legal if end of aa not inclusive -create table ggg (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition bb start (date '2008-01-01') end (date '2009-01-01'), -partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive -); -ERROR: starting value of partition "bb" overlaps previous range -LINE 5: partition bb start (date '2008-01-01') end (date '2009-01-01... - ^ -drop table ggg cascade; -ERROR: table "ggg" does not exist --- legal because start of bb not inclusive -create table ggg (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition bb start (date '2008-01-01') exclusive end (date '2009-01-01'), -partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -drop table ggg cascade; --- validate aa - start greater than end -create table ggg (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition bb start (date '2008-01-01') end (date '2009-01-01'), -partition aa start (date '2007-01-01') end (date '2006-01-01') -); -ERROR: START greater than END for partition "aa" -LINE 6: partition aa start (date '2007-01-01') end (date '2006-01-01... - ^ -drop table ggg cascade; -ERROR: table "ggg" does not exist --- formerly we could not set end of first partition because next is before --- but we can sort them now so this is legal. -create table ggg (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition bb start (date '2008-01-01') , -partition aa start (date '2007-01-01') -); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_bb" for table "ggg" -drop table ggg cascade; --- test cross type coercion --- int -> char(N) -create table ggg (i int, a char(1)) -distributed by (i) -partition by list(a) -(partition aa values(1, 2)); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -drop table ggg cascade; --- int -> numeric -create table ggg (i int, n numeric(20, 2)) -distributed by (i) -partition by list(n) -(partition aa values(1.22, 4.1)); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_aa" for table "ggg" -drop table ggg cascade; --- EVERY --- the documentation example, rewritten with EVERY in a template -CREATE TABLE rank (id int, -rank int, year date, gender char(1)) -DISTRIBUTED BY (id, gender, year) -partition by list (gender) -subpartition by range (year) -subpartition template ( -start (date '2001-01-01') -end (date '2006-01-01') every (interval '1 year')) ( -partition boys values ('M'), -partition girls values ('F') -); -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys" for table "rank" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls" for table "rank" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_1" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_2" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_3" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_4" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_boys_2_prt_5" for table "rank_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_1" for table "rank_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_2" for table "rank_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_3" for table "rank_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_4" for table "rank_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_5" for table "rank_1_prt_girls" -insert into rank values (1, 1, date '2001-01-15', 'M'); -insert into rank values (2, 1, date '2002-02-15', 'M'); -insert into rank values (3, 1, date '2003-03-15', 'M'); -insert into rank values (4, 1, date '2004-04-15', 'M'); -insert into rank values (5, 1, date '2005-05-15', 'M'); -insert into rank values (6, 1, date '2001-01-15', 'F'); -insert into rank values (7, 1, date '2002-02-15', 'F'); -insert into rank values (8, 1, date '2003-03-15', 'F'); -insert into rank values (9, 1, date '2004-04-15', 'F'); -insert into rank values (10, 1, date '2005-05-15', 'F'); -select * from rank order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 1 | 1 | 01-15-2001 | M - 2 | 1 | 02-15-2002 | M - 3 | 1 | 03-15-2003 | M - 4 | 1 | 04-15-2004 | M - 5 | 1 | 05-15-2005 | M - 6 | 1 | 01-15-2001 | F - 7 | 1 | 02-15-2002 | F - 8 | 1 | 03-15-2003 | F - 9 | 1 | 04-15-2004 | F - 10 | 1 | 05-15-2005 | F -(10 rows) - -select * from rank_1_prt_boys order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 1 | 1 | 01-15-2001 | M - 2 | 1 | 02-15-2002 | M - 3 | 1 | 03-15-2003 | M - 4 | 1 | 04-15-2004 | M - 5 | 1 | 05-15-2005 | M -(5 rows) - -select * from rank_1_prt_girls order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 6 | 1 | 01-15-2001 | F - 7 | 1 | 02-15-2002 | F - 8 | 1 | 03-15-2003 | F - 9 | 1 | 04-15-2004 | F - 10 | 1 | 05-15-2005 | F -(5 rows) - -select * from rank_1_prt_girls_2_prt_1 order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 6 | 1 | 01-15-2001 | F -(1 row) - -select * from rank_1_prt_girls_2_prt_2 order by 1, 2, 3, 4; - id | rank | year | gender -----+------+------------+-------- - 7 | 1 | 02-15-2002 | F -(1 row) - -drop table rank cascade; --- integer ranges work too -create table ggg (id integer, a integer) -distributed by (id) -partition by range (a) -(start (1) end (10) every (1)); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_3" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_4" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_5" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_6" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_7" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_8" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_9" for table "ggg" -insert into ggg values (1, 1); -insert into ggg values (2, 2); -insert into ggg values (3, 3); -insert into ggg values (4, 4); -insert into ggg values (5, 5); -insert into ggg values (6, 6); -insert into ggg values (7, 7); -insert into ggg values (8, 8); -insert into ggg values (9, 9); -insert into ggg values (10, 10); -ERROR: no partition for partitioning key (seg7 localhost:40000 pid=55503) -select * from ggg order by 1, 2; - id | a -----+--- - 1 | 1 - 2 | 2 - 3 | 3 - 4 | 4 - 5 | 5 - 6 | 6 - 7 | 7 - 8 | 8 - 9 | 9 -(9 rows) - -select * from ggg_1_prt_1 order by 1, 2; - id | a -----+--- - 1 | 1 -(1 row) - -select * from ggg_1_prt_2 order by 1, 2; - id | a -----+--- - 2 | 2 -(1 row) - -select * from ggg_1_prt_3 order by 1, 2; - id | a -----+--- - 3 | 3 -(1 row) - -select * from ggg_1_prt_4 order by 1, 2; - id | a -----+--- - 4 | 4 -(1 row) - -drop table ggg cascade; --- hash tests -create table ggg (a char(1), b varchar(2), d varchar(2)) -distributed by (a) -partition by hash(b) -partitions 3 -(partition a, partition b, partition c); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_a" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_b" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_c" for table "ggg" -insert into ggg values (1,1,1); -insert into ggg values (2,2,1); -insert into ggg values (1,3,1); -insert into ggg values (2,2,3); -insert into ggg values (1,4,5); -insert into ggg values (2,2,4); -insert into ggg values (1,5,6); -insert into ggg values (2,7,3); -insert into ggg values (1,'a','b'); -insert into ggg values (2,'c','c'); -select * from ggg order by 1, 2, 3; - a | b | d ----+---+--- - 1 | 1 | 1 - 1 | 3 | 1 - 1 | 4 | 5 - 1 | 5 | 6 - 1 | a | b - 2 | 2 | 1 - 2 | 2 | 3 - 2 | 2 | 4 - 2 | 7 | 3 - 2 | c | c -(10 rows) - ---select * from ggg_1_prt_a order by 1, 2, 3; ---select * from ggg_1_prt_b order by 1, 2, 3; ---select * from ggg_1_prt_c order by 1, 2, 3; -drop table ggg cascade; --- use multiple cols -create table ggg (a char(1), b varchar(2), d varchar(2)) -distributed by (a) -partition by hash(b,d) -partitions 3 -(partition a, partition b, partition c); -ERROR: Composite partition keys are not allowed --- append only tests -create table foz (i int, d date) with (appendonly = true) distributed by (i) -partition by range (d) (start (date '2001-01-01') end (date '2005-01-01') -every(interval '1 year')); -NOTICE: CREATE TABLE will create partition "foz_1_prt_1" for table "foz" -NOTICE: CREATE TABLE will create partition "foz_1_prt_2" for table "foz" -NOTICE: CREATE TABLE will create partition "foz_1_prt_3" for table "foz" -NOTICE: CREATE TABLE will create partition "foz_1_prt_4" for table "foz" -insert into foz select i, '2001-01-01'::date + ('1 day'::interval * i) from -generate_series(1, 1000) i; -select count(*) from foz; - count -------- - 1000 -(1 row) - -select count(*) from foz_1_prt_1; - count -------- - 364 -(1 row) - -select min(d), max(d) from foz; - min | max -------------+------------ - 01-02-2001 | 09-28-2003 -(1 row) - -select min(d), max(d) from foz_1_prt_1; - min | max -------------+------------ - 01-02-2001 | 12-31-2001 -(1 row) - -select min(d), max(d) from foz_1_prt_2; - min | max -------------+------------ - 01-01-2002 | 12-31-2002 -(1 row) - -select min(d), max(d) from foz_1_prt_3; - min | max -------------+------------ - 01-01-2003 | 09-28-2003 -(1 row) - -select min(d), max(d) from foz_1_prt_4; - min | max ------+----- - | -(1 row) - -drop table foz cascade; --- complain if create table as select (CTAS) -CREATE TABLE rank1 (id int, -rank int, year date, gender char(1)); -create table rank2 as select * from rank1 -DISTRIBUTED BY (id, gender, year) -partition by list (gender) -subpartition by range (year) -subpartition template ( -start (date '2001-01-01') -end (date '2006-01-01') every (interval '1 year')) ( -partition boys values ('M'), -partition girls values ('F') -); -ERROR: Cannot create a partitioned table using CREATE TABLE AS SELECT -HINT: Use CREATE TABLE...LIKE (followed by INSERT...SELECT) instead --- like is ok -create table rank2 (like rank1) -DISTRIBUTED BY (id, gender, year) -partition by list (gender) -subpartition by range (year) -subpartition template ( -start (date '2001-01-01') -end (date '2006-01-01') every (interval '1 year')) ( -partition boys values ('M'), -partition girls values ('F') -); -NOTICE: CREATE TABLE will create partition "rank2_1_prt_boys" for table "rank2" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_girls" for table "rank2" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_boys_2_prt_1" for table "rank2_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_boys_2_prt_2" for table "rank2_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_boys_2_prt_3" for table "rank2_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_boys_2_prt_4" for table "rank2_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_boys_2_prt_5" for table "rank2_1_prt_boys" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_girls_2_prt_1" for table "rank2_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_girls_2_prt_2" for table "rank2_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_girls_2_prt_3" for table "rank2_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_girls_2_prt_4" for table "rank2_1_prt_girls" -NOTICE: CREATE TABLE will create partition "rank2_1_prt_girls_2_prt_5" for table "rank2_1_prt_girls" -drop table rank1 cascade; -drop table rank2 cascade; --- alter table testing -create table hhh (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition aa start (date '2007-01-01') end (date '2008-01-01') - with (appendonly=true), -partition bb start (date '2008-01-01') end (date '2009-01-01') - with (appendonly=true) -); -NOTICE: CREATE TABLE will create partition "hhh_1_prt_aa" for table "hhh" -NOTICE: CREATE TABLE will create partition "hhh_1_prt_bb" for table "hhh" --- already exists -alter table hhh add partition aa; -ERROR: partition "aa" of relation "hhh" already exists --- no partition spec -alter table hhh add partition cc; -ERROR: missing boundary specification --- overlaps -alter table hhh add partition cc start ('2008-01-01') end ('2010-01-01'); -ERROR: new partition overlaps existing partition "bb" -alter table hhh add partition cc end ('2008-01-01'); -ERROR: new partition overlaps existing partition --- reversed (start > end) -alter table hhh add partition cc start ('2010-01-01') end ('2009-01-01'); -ERROR: START greater than END for partition "cc" --- works ---alter table hhh add partition cc start ('2009-01-01') end ('2010-01-01'); -alter table hhh add partition cc end ('2010-01-01'); -NOTICE: CREATE TABLE will create partition "hhh_1_prt_cc" for table "hhh" --- works - anonymous partition MPP-3350 -alter table hhh add partition end ('2010-02-01'); -NOTICE: CREATE TABLE will create partition "hhh_1_prt_r671594530" for table "hhh" --- MPP-3607 - ADD PARTITION with open intervals -create table no_end1 (aa int, bb int) partition by range (bb) -(partition foo start(3)); -NOTICE: CREATE TABLE will create partition "no_end1_1_prt_foo" for table "no_end1" --- fail overlap -alter table no_end1 add partition baz end (4); -ERROR: new partition overlaps existing partition --- fail overlap (because prior partition has no end) -alter table no_end1 add partition baz start (5); -ERROR: new partition overlaps existing partition --- ok (terminates on foo start) -alter table no_end1 add partition baz start (2); -NOTICE: CREATE TABLE will create partition "no_end1_1_prt_baz" for table "no_end1" --- ok (because ends before baz start) -alter table no_end1 add partition baz2 end (1); -NOTICE: CREATE TABLE will create partition "no_end1_1_prt_baz2" for table "no_end1" -create table no_start1 (aa int, bb int) partition by range (bb) -(partition foo end(3)); -NOTICE: CREATE TABLE will create partition "no_start1_1_prt_foo" for table "no_start1" --- fail overlap (because next partition has no start) -alter table no_start1 add partition baz start (2); -ERROR: new partition overlaps existing partition --- fail overlap (because next partition has no start) -alter table no_start1 add partition baz end (1); -ERROR: new partition overlaps existing partition --- ok (starts on foo end) -alter table no_start1 add partition baz end (4); -NOTICE: CREATE TABLE will create partition "no_start1_1_prt_baz" for table "no_start1" --- ok (because starts after baz end) -alter table no_start1 add partition baz2 start (5); -NOTICE: CREATE TABLE will create partition "no_start1_1_prt_baz2" for table "no_start1" -select tablename, partitionlevel, parentpartitiontablename, -partitionname, partitionrank, partitionboundary from pg_partitions -where tablename = 'no_start1' or tablename = 'no_end1' -order by tablename, partitionrank; - tablename | partitionlevel | parentpartitiontablename | partitionname | partitionrank | partitionboundary ------------+----------------+--------------------------+---------------+---------------+-------------------------------------------------------- - no_end1 | 0 | | baz2 | 1 | PARTITION baz2 END (1) WITH (appendonly=true) - no_end1 | 0 | | baz | 2 | PARTITION baz START (2) END (3) WITH (appendonly=true) - no_end1 | 0 | | foo | 3 | PARTITION foo START (3) WITH (appendonly=true) - no_start1 | 0 | | foo | 1 | PARTITION foo END (3) WITH (appendonly=true) - no_start1 | 0 | | baz | 2 | PARTITION baz START (3) END (4) WITH (appendonly=true) - no_start1 | 0 | | baz2 | 3 | PARTITION baz2 START (5) WITH (appendonly=true) -(6 rows) - -drop table no_end1; -drop table no_start1; --- hash partitions cannot have default partitions -create table jjj (aa int, bb int) -partition by hash(bb) -(partition j1, partition j2); -NOTICE: CREATE TABLE will create partition "jjj_1_prt_j1" for table "jjj" -NOTICE: CREATE TABLE will create partition "jjj_1_prt_j2" for table "jjj" -alter table jjj add default partition; -ERROR: syntax error at or near ";" -LINE 1: alter table jjj add default partition; - ^ -drop table jjj cascade; --- default partitions cannot have boundary specifications -create table jjj (aa int, bb date) -partition by range(bb) -(partition j1 end (date '2008-01-01'), -partition j2 end (date '2009-01-01')); -NOTICE: CREATE TABLE will create partition "jjj_1_prt_j1" for table "jjj" -NOTICE: CREATE TABLE will create partition "jjj_1_prt_j2" for table "jjj" --- must have a name -alter table jjj add default partition; -ERROR: syntax error at or near ";" -LINE 1: alter table jjj add default partition; - ^ -alter table jjj add default partition for (rank(1)); -ERROR: Can only ADD a partition by name --- cannot have boundary spec -alter table jjj add default partition j3 end (date '2010-01-01'); -ERROR: invalid use of boundary specification for DEFAULT partition "j3" of relation "jjj" -drop table jjj cascade; --- only one default partition -create table jjj (aa int, bb date) -partition by range(bb) -(partition j1 end (date '2008-01-01'), -partition j2 end (date '2009-01-01'), -default partition j3); -NOTICE: CREATE TABLE will create partition "jjj_1_prt_j3" for table "jjj" -NOTICE: CREATE TABLE will create partition "jjj_1_prt_j1" for table "jjj" -NOTICE: CREATE TABLE will create partition "jjj_1_prt_j2" for table "jjj" -alter table jjj add default partition j3 ; -ERROR: partition "j3" of relation "jjj" already exists -alter table jjj add default partition j4 ; -ERROR: DEFAULT partition "j3" for relation "jjj" already exists --- cannot add if have default, must split -alter table jjj add partition j5 end (date '2010-01-01'); -ERROR: cannot add RANGE partition "j5" to relation "jjj" with DEFAULT partition "j3" -HINT: need to SPLIT partition "j3" -drop table jjj cascade; -alter table hhh alter partition cc set tablespace foo_p; -ERROR: tablespace "foo_p" does not exist -alter table hhh alter partition aa set tablespace foo_p; -ERROR: tablespace "foo_p" does not exist -alter table hhh coalesce partition cc; -ERROR: cannot COALESCE PARTITION for relation "hhh" -alter table hhh coalesce partition aa; -ERROR: cannot COALESCE PARTITION for relation "hhh" -alter table hhh drop partition cc; -alter table hhh drop partition cc cascade; -ERROR: partition "cc" of relation "hhh" does not exist -alter table hhh drop partition cc restrict; -ERROR: partition "cc" of relation "hhh" does not exist -alter table hhh drop partition if exists cc; -NOTICE: partition "cc" of relation "hhh" does not exist, skipping --- fail (mpp-3265) -alter table hhh drop partition for (rank(0)); -ERROR: partition for rank 0 of relation "hhh" does not exist -alter table hhh drop partition for (rank(-55)); -ERROR: partition for rank -55 of relation "hhh" does not exist -alter table hhh drop partition for ('2001-01-01'); -ERROR: partition for value ('2001-01-01') of relation "hhh" does not exist -create table hhh_r1 (a char(1), b date, d char(3)) -distributed by (a) -partition by range (b) -( -partition aa start (date '2007-01-01') end (date '2008-01-01') - every (interval '1 month') -); -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_1" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_2" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_3" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_4" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_5" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_6" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_7" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_8" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_9" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_10" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_11" for table "hhh_r1" -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_aa_12" for table "hhh_r1" -create table hhh_l1 (a char(1), b date, d char(3)) -distributed by (a) -partition by list (b) -( -partition aa values ('2007-01-01'), -partition bb values ('2008-01-01'), -partition cc values ('2009-01-01') -); -NOTICE: CREATE TABLE will create partition "hhh_l1_1_prt_aa" for table "hhh_l1" -NOTICE: CREATE TABLE will create partition "hhh_l1_1_prt_bb" for table "hhh_l1" -NOTICE: CREATE TABLE will create partition "hhh_l1_1_prt_cc" for table "hhh_l1" --- must have name or value for list partition -alter table hhh_l1 drop partition; -ERROR: missing name or value for DROP for relation "hhh_l1" -alter table hhh_l1 drop partition aa; -alter table hhh_l1 drop partition for ('2008-01-01'); -NOTICE: dropped partition "bb" for relation "hhh_l1" --- if not specified, drop first range partition... -alter table hhh_r1 drop partition for ('2007-04-01'); -NOTICE: dropped partition "aa_4" for relation "hhh_r1" -alter table hhh_r1 drop partition; -NOTICE: dropped partition "aa_1" for relation "hhh_r1" -alter table hhh_r1 drop partition; -NOTICE: dropped partition "aa_2" for relation "hhh_r1" -alter table hhh_r1 drop partition; -NOTICE: dropped partition "aa_3" for relation "hhh_r1" -alter table hhh_r1 drop partition; -NOTICE: dropped partition "aa_5" for relation "hhh_r1" -alter table hhh_r1 drop partition; -NOTICE: dropped partition "aa_6" for relation "hhh_r1" --- more add partition tests --- start before first partition (fail because start equal end) -alter table hhh_r1 add partition zaa start ('2007-07-01'); -ERROR: new partition overlaps existing partition --- start before first partition (ok) -alter table hhh_r1 add partition zaa start ('2007-06-01'); -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_zaa" for table "hhh_r1" --- start > last (fail because start equal end) -alter table hhh_r1 add partition bb start ('2008-01-01') end ('2008-01-01') ; -ERROR: START equal to END for partition "bb" --- start > last (ok) -alter table hhh_r1 add partition bb start ('2008-01-01') -end ('2008-02-01') inclusive; -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_bb" for table "hhh_r1" --- start > last (fail because start == last end inclusive) -alter table hhh_r1 add partition cc start ('2008-02-01') end ('2008-03-01') ; -ERROR: new partition overlaps existing partition "bb" --- start > last (ok [and leave a gap]) -alter table hhh_r1 add partition cc start ('2008-04-01') end ('2008-05-01') ; -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_cc" for table "hhh_r1" --- overlap (fail) -alter table hhh_r1 add partition dd start ('2008-01-01') end ('2008-05-01') ; -ERROR: new partition overlaps existing partition "bb" --- new partition in "gap" (ok) -alter table hhh_r1 add partition dd start ('2008-03-01') end ('2008-04-01') ; -NOTICE: CREATE TABLE will create partition "hhh_r1_1_prt_dd" for table "hhh_r1" --- overlap all partitions (fail) -alter table hhh_r1 add partition ee start ('2006-01-01') end ('2009-01-01') ; -ERROR: new partition overlaps existing partition --- start before first partition (fail because end in "gap" [and overlaps]) -alter table hhh_r1 add partition yaa start ('2007-05-01') end ('2007-07-01'); -ERROR: new partition overlaps existing partition --- start before first partition (fail ) -alter table hhh_r1 add partition yaa start ('2007-05-01') -end ('2007-10-01') inclusive; -ERROR: new partition overlaps existing partition "aa_10" --- start before first partition (fail because end overlaps) -alter table hhh_r1 add partition yaa start ('2007-05-01') -end ('2007-10-01') exclusive; -ERROR: new partition overlaps existing partition -drop table hhh_r1 cascade; -drop table hhh_l1 cascade; --- SPLIT tests --- basic sanity tests. All should pass. -create table k (i int) partition by range(i) (start(1) end(10) every(2), -default partition mydef); -NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_3" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_4" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_5" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_6" for table "k" -insert into k select i from generate_series(1, 100) i; -alter table k split partition mydef at (20) into (partition mydef, -partition foo); -ERROR: AT clause cannot be used when splitting a default RANGE partition -drop table k; -create table j (i int) partition by list(i) (partition a values(1, 2, 3, 4), -partition b values(5, 6, 7, 8)); -NOTICE: CREATE TABLE will create partition "j_1_prt_a" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_b" for table "j" -insert into j select i from generate_series(1, 8) i; -alter table j split partition for(1) at (2, 3) into (partition fa, partition -fb); -NOTICE: exchanged partition "a" of relation "j" with relation "pg_temp_19493" -NOTICE: dropped partition "a" for relation "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_fa" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_fb" for table "j" -select * from j_1_prt_fa; - i ---- - 4 - 1 -(2 rows) - -select * from j_1_prt_fb; - i ---- - 3 - 2 -(2 rows) - -alter table j split partition for(5) at (6); -NOTICE: exchanged partition "b" of relation "j" with relation "pg_temp_19493" -NOTICE: dropped partition "b" for relation "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_r110764385" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_r1253749698" for table "j" -select * from j; - i ---- - 4 - 1 - 3 - 2 - 7 - 5 - 8 - 6 -(8 rows) - --- should fail -alter table j split partition for (1) at (100); -ERROR: AT clause parameter is not a member of the target partition specification -drop table j; -create table k (i int) partition by range(i) (start(1) end(10) every(2), -default partition mydef); -NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_3" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_4" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_5" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_6" for table "k" --- should fail -alter table k split default partition start(30) end (300) into (partition mydef, partition mydef); -ERROR: both INTO partitions already exist -alter table k split partition for(3) at (20); -ERROR: AT clause parameter is not a member of the target partition specification -drop table k; --- should work -create table k (i int) partition by range(i) (start(1) end(10) every(2), -default partition mydef); -NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_3" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_4" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_5" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_6" for table "k" -insert into k select i from generate_series(1, 30) i; -alter table k split default partition start(15) end(20) into -(partition mydef, partition foo); -NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_19600" -NOTICE: dropped partition "mydef" for relation "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_foo" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" -select * from k_1_prt_foo; - i ----- - 16 - 19 - 17 - 15 - 18 -(5 rows) - -alter table k split default partition start(22) exclusive end(25) inclusive -into (partition bar, partition mydef); -NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_19600" -NOTICE: dropped partition "mydef" for relation "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_bar" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" -select * from k_1_prt_bar; - i ----- - 24 - 25 - 23 -(3 rows) - -alter table k split partition bar at (23) into (partition baz, partition foz); -NOTICE: exchanged partition "bar" of relation "k" with relation "pg_temp_19600" -NOTICE: dropped partition "bar" for relation "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_baz" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_foz" for table "k" -select partitiontablename,partitionposition,partitionrangestart, - partitionrangeend from pg_partitions where tablename = 'k' - order by partitionposition; - partitiontablename | partitionposition | partitionrangestart | partitionrangeend ---------------------+-------------------+---------------------+------------------- - k_1_prt_mydef | 0 | | - k_1_prt_2 | 2 | 1 | 3 - k_1_prt_3 | 3 | 3 | 5 - k_1_prt_4 | 4 | 5 | 7 - k_1_prt_5 | 5 | 7 | 9 - k_1_prt_6 | 6 | 9 | 10 - k_1_prt_foo | 7 | 15 | 20 - k_1_prt_baz | 8 | 22 | 23 - k_1_prt_foz | 9 | 23 | 25 -(9 rows) - -drop table k; --- Test errors for default handling -create table k (i int) partition by range(i) (start(1) end(2), -default partition mydef); -NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" -alter table k split partition mydef at (25) into (partition foo, partition -mydef); -ERROR: AT clause cannot be used when splitting a default RANGE partition -drop table k; --- check that when we split a default, the INTO clause must named the default -create table k (i date) partition by range(i) (start('2008-01-01') -end('2009-01-01') every(interval '1 month'), default partition default_part); -NOTICE: CREATE TABLE will create partition "k_1_prt_default_part" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_3" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_4" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_5" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_6" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_7" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_8" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_9" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_10" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_11" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_12" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_13" for table "k" -alter table k split default partition start ('2009-01-01') end ('2009-02-01') -into (partition aa, partition nodate); -ERROR: default partition name missing from INTO clause -alter table k split default partition start ('2009-01-01') end ('2009-02-01') -into (partition aa, partition default_part); -NOTICE: exchanged partition "default_part" of relation "k" with relation "pg_temp_19722" -NOTICE: dropped partition "default_part" for relation "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_aa" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_default_part" for table "k" --- check that it works without INTO -alter table k split default partition start ('2009-02-01') end ('2009-03-01'); -NOTICE: exchanged partition "default_part" of relation "k" with relation "pg_temp_19722" -NOTICE: dropped partition "default_part" for relation "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_r520735789" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_default_part" for table "k" -drop table k; --- List too -create table k (i int) partition by list(i) (partition a values(1, 2), -partition b values(3, 4), default partition mydef); -NOTICE: CREATE TABLE will create partition "k_1_prt_a" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_b" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" -alter table k split partition mydef at (5) into (partition foo, partition bar); -ERROR: default partition name missing from INTO clause -alter table k split partition mydef at (5) into (partition foo, partition mydef); -NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_19854" -NOTICE: dropped partition "mydef" for relation "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_foo" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" -alter table k split partition mydef at (10); -NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_19854" -NOTICE: dropped partition "mydef" for relation "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_r471114786" for table "k" -NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" -drop table k; --- For LIST, make sure that we reject AT() clauses which match all parameters -create table j (i int) partition by list(i) (partition a values(1, 2, 3, 4), - partition b values(5, 6, 7, 8)); -NOTICE: CREATE TABLE will create partition "j_1_prt_a" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_b" for table "j" -alter table j split partition for(1) at (1,2) into (partition fa, partition fb); -NOTICE: exchanged partition "a" of relation "j" with relation "pg_temp_19916" -NOTICE: dropped partition "a" for relation "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_fa" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_fb" for table "j" -alter table j split partition for(1) at (1,2) -into (partition f1a, partition f1b); -- This has partition rules that overlaps -ERROR: AT clause cannot contain all values in partition "fb" -drop table j; --- Check that we can split LIST partitions that have a default partition -create table j (i int) partition by list(i) (partition a values(1, 2, 3, 4), -partition b values(5, 6, 7, 8), default partition default_part); -NOTICE: CREATE TABLE will create partition "j_1_prt_a" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_b" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_default_part" for table "j" -alter table j split partition for(1) at (1,2) into (partition f1a, partition -f1b); -NOTICE: exchanged partition "a" of relation "j" with relation "pg_temp_19956" -NOTICE: dropped partition "a" for relation "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_f1a" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_f1b" for table "j" -drop table j; --- Make sure range can too -create table j (i int) partition by range(i) (partition a start(1) end(10), -default partition default_part); -NOTICE: CREATE TABLE will create partition "j_1_prt_default_part" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_a" for table "j" -alter table j split partition for(1) at (5) into (partition f1a, partition f1b); -NOTICE: exchanged partition "a" of relation "j" with relation "pg_temp_20002" -NOTICE: dropped partition "a" for relation "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_f1a" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_f1b" for table "j" -drop table j; --- GPSQL-285 -- GPSQL-277 -create table pt_table (a int, b int, c int, d int) distributed by (a) partition by range(b) (default partition others, start(1) end(5) every(1)); -NOTICE: CREATE TABLE will create partition "pt_table_1_prt_others" for table "pt_table" -NOTICE: CREATE TABLE will create partition "pt_table_1_prt_2" for table "pt_table" -NOTICE: CREATE TABLE will create partition "pt_table_1_prt_3" for table "pt_table" -NOTICE: CREATE TABLE will create partition "pt_table_1_prt_4" for table "pt_table" -NOTICE: CREATE TABLE will create pa
<TRUNCATED>
