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" 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_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" ERROR: column "alsodoesntexist" does not exist in relation "ggg_1_prt_1" LINE 5: subpartition by list(alsodoesntexist) ^ -- should work 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_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" for table "ggg" 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; -- 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_aa_2_prt_cc" 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" for table "ggg_1_prt_aa" 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" for table "ggg" 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_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" for table "ggg_1_prt_bb" 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 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 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_aa_2_prt_cc" 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" for table "ggg_1_prt_aa" 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" for table "ggg" 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_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" for table "ggg_1_prt_bb" 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; -- 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) 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_aa_2_prt_cc" 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" for table "ggg_1_prt_aa" 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" for table "ggg" 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), ^ -- too many columns for RANGE partition 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: too many columns for RANGE partition -- only one column is allowed. LINE 3: partition by range (b,d) ^ drop table ggg cascade; ERROR: table "ggg" does not exist -- 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_1_2_prt_1" 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_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_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_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" 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_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" 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_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_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" 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_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" 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_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_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_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" 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_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" 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_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_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" 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_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" for table "ggg_1_prt_1" 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_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_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_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" 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_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" 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_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_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" 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_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" 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_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_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_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" 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_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" 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_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_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" 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_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" for table "ggg" 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_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_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_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_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" 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_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" 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_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_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" 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_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" 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_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_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_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" 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_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" 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_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_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" 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_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" for table "ggg_1_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_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_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_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" 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_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" 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_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_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" 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_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" 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_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_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_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" 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_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" 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_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_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" 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_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_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" for table "ggg" 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_aa_2_prt_cc" 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" for table "ggg_1_prt_aa" 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" for table "ggg" 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_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" for table "ggg_1_prt_bb" 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_aa_2_prt_cc" 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" for table "ggg_1_prt_aa" 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" for table "ggg" 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_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" for table "ggg_1_prt_bb" 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_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" for table "rank" 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 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_aa_2_prt_dd" 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" for table "ggg_1_prt_aa" 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" for table "ggg" 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_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" for table "ggg_1_prt_bb" 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'), ^ -- duplicate values CREATE TABLE rank (id int, rank int, year date, gender char(1)) DISTRIBUTED BY (id, gender, year) partition by list (rank,gender) ( values ((1, 'M')), values ((2, 'M')), values ((3, 'M')), values ((1, 'F')), partition ff values ((4, 'M')), partition bb values ((1, 'M')) ); ERROR: duplicate VALUES in partition "bb" LINE 10: partition bb values ((1, 'M')) ^ -- 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_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" for table "rank" 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 (seg1 xzhangmac:40001 pid=94564) 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); 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 of different types and without a partition spec create table ggg (a char(1), b varchar(2), d integer, e date) distributed by (a) partition by hash(b,d,e) partitions 3; 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" insert into ggg values (1,1,1,date '2001-01-15'); insert into ggg values (2,2,1,date '2001-01-15'); insert into ggg values (1,3,1,date '2001-01-15'); insert into ggg values (2,2,3,date '2001-01-15'); insert into ggg values (1,4,5,date '2001-01-15'); insert into ggg values (2,2,4,date '2001-01-15'); insert into ggg values (1,5,6,date '2001-01-15'); insert into ggg values (2,7,3,date '2001-01-15'); insert into ggg values (1,'a',33,date '2001-01-15'); insert into ggg values (2,'c',44,date '2001-01-15'); select * from ggg order by 1, 2, 3, 4; a | b | d | e ---+---+----+------------ 1 | 1 | 1 | 01-15-2001 1 | 3 | 1 | 01-15-2001 1 | 4 | 5 | 01-15-2001 1 | 5 | 6 | 01-15-2001 1 | a | 33 | 01-15-2001 2 | 2 | 1 | 01-15-2001 2 | 2 | 3 | 01-15-2001 2 | 2 | 4 | 01-15-2001 2 | 7 | 3 | 01-15-2001 2 | c | 44 | 01-15-2001 (10 rows) --select * from ggg_1_prt_1 order by 1, 2, 3, 4; --select * from ggg_1_prt_2 order by 1, 2, 3, 4; --select * from ggg_1_prt_3 order by 1, 2, 3, 4; drop table ggg cascade; create table ggg (a char(1), b char(2), d char(3)) distributed by (a) partItion by hash(b) partitions 3; 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" drop table ggg cascade; 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), ^ drop table ggg cascade; ERROR: table "ggg" does not exist 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_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" for table "ggg" 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; 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; 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: too many columns for RANGE partition -- only one column is allowed. LINE 3: partition by range (b,d) ^ drop table ggg cascade; ERROR: table "ggg" does not exist 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_aa_2_prt_dd" 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" for table "ggg_1_prt_aa" 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" for table "ggg" 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_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" for table "ggg_1_prt_bb" 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; 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 create table ggg (a char(1), b varchar(2), d varchar(2)) distributed by (a) partition by hash(b) partitions 3; 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" 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; a | b | d ---+---+--- 1 | 1 | 1 1 | 3 | 1 1 | 4 | 5 1 | 5 | 6 1 | a | b 2 | 7 | 3 2 | 2 | 1 2 | 2 | 3 2 | 2 | 4 2 | c | c (10 rows) select * from ggg_1_prt_1; a | b | d ---+---+--- 1 | 1 | 1 1 | 3 | 1 2 | 7 | 3 (3 rows) select * from ggg_1_prt_2; a | b | d ---+---+--- 1 | 4 | 5 (1 row) select * from ggg_1_prt_3; a | b | d ---+---+--- 2 | 2 | 1 2 | 2 | 3 2 | 2 | 4 2 | c | c 1 | 5 | 6 1 | a | b (6 rows) drop table ggg cascade; -- 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; -- copy test create table foz (i int, d date) 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" COPY foz FROM stdin DELIMITER '|'; select * from foz_1_prt_1; i | d ---+------------ 1 | 01-02-2001 2 | 10-10-2001 (2 rows) select * from foz_1_prt_2; i | d ---+------------ 3 | 10-30-2002 (1 row) select * from foz_1_prt_3; i | d ---+------------ 4 | 01-01-2003 (1 row) select * from foz_1_prt_4; i | d ---+------------ 5 | 05-05-2004 (1 row) -- Check behaviour of key for which there is no partition COPY foz FROM stdin DELIMITER '|'; ERROR: no partition for partitioning key CONTEXT: COPY foz, line 1: "6|2010-01-01" drop table foz cascade; -- Same test with append only 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" COPY foz FROM stdin DELIMITER '|'; select * from foz_1_prt_1; i | d ---+------------ 2 | 10-10-2001 1 | 01-02-2001 (2 rows) select * from foz_1_prt_2; i | d ---+------------ 3 | 10-30-2002 (1 row) select * from foz_1_prt_3; i | d ---+------------ 4 | 01-01-2003 (1 row) select * from foz_1_prt_4; i | d ---+------------ 5 | 05-05-2004 (1 row) -- Check behaviour of key for which there is no partition COPY foz FROM stdin DELIMITER '|'; ERROR: no partition for partitioning key CONTEXT: COPY foz, line 1: "6|2010-01-01" drop table foz cascade; -- complain if create table as select (CTAS) CREATE TABLE rank1 (id int, rank int, year date, gender char(1)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. create table 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_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" for table "rank2" 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=false) ); 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_r1244790963" 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: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. 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: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. 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) no_end1 | 0 | | baz | 2 | PARTITION baz START (2) END (3) no_end1 | 0 | | foo | 3 | PARTITION foo START (3) no_start1 | 0 | | foo | 1 | PARTITION foo END (3) no_start1 | 0 | | baz | 2 | PARTITION baz START (3) END (4) no_start1 | 0 | | baz2 | 3 | PARTITION baz2 START (5) (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: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. 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: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. 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: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. 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 "aa_7" -- 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 "aa_10" drop table hhh_r1 cascade; drop table hhh_l1 cascade; -- the documentation example, rewritten with EVERY in a template -- and also with a default partition 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'), default partition neuter ); NOTICE: CREATE TABLE will create partition "rank_1_prt_boys" 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" for table "rank" 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" NOTICE: CREATE TABLE will create partition "rank_1_prt_neuter" for table "rank" NOTICE: CREATE TABLE will create partition "rank_1_prt_neuter_2_prt_1" for table "rank_1_prt_neuter" NOTICE: CREATE TABLE will create partition "rank_1_prt_neuter_2_prt_2" for table "rank_1_prt_neuter" NOTICE: CREATE TABLE will create partition "rank_1_prt_neuter_2_prt_3" for table "rank_1_prt_neuter" NOTICE: CREATE TABLE will create partition "rank_1_prt_neuter_2_prt_4" for table "rank_1_prt_neuter" NOTICE: CREATE TABLE will create partition "rank_1_prt_neuter_2_prt_5" for table "rank_1_prt_neuter" 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 ; id | rank | year | gender ----+------+------------+-------- 1 | 1 | 01-15-2001 | M 3 | 1 | 03-15-2003 | M 4 | 1 | 04-15-2004 | M 6 | 1 | 01-15-2001 | F 8 | 1 | 03-15-2003 | F 9 | 1 | 04-15-2004 | F 2 | 1 | 02-15-2002 | M 5 | 1 | 05-15-2005 | M 7 | 1 | 02-15-2002 | F 10 | 1 | 05-15-2005 | F (10 rows) alter table rank DROP partition boys restrict; NOTICE: dropped partition "boys" for relation "rank" and its children select * from rank ; id | rank | year | gender ----+------+------------+-------- 6 | 1 | 01-15-2001 | F 8 | 1 | 03-15-2003 | F 9 | 1 | 04-15-2004 | F 7 | 1 | 02-15-2002 | F 10 | 1 | 05-15-2005 | F (5 rows) -- MPP-3722: complain if for(value) matches the default partition alter table rank truncate partition for('N'); ERROR: FOR expression matches DEFAULT partition "neuter" of relation "rank" HINT: FOR expression may only specify a non-default partition in this context. alter table rank DROP partition for('N'); ERROR: FOR expression matches DEFAULT partition "neuter" of relation "rank" HINT: FOR expression may only specify a non-default partition in this context. alter table rank DROP partition if exists for('N'); ERROR: FOR expression matches DEFAULT partition "neuter" of relation "rank" HINT: FOR expression may only specify a non-default partition in this context. alter table rank DROP default partition if exists ; NOTICE: dropped partition "neuter" for relation "rank" and its children -- can't drop the final partition - must drop the table alter table rank DROP partition girls; ERROR: cannot drop partition "girls" of relation "rank" -- only one remains HINT: Use DROP TABLE "rank" to remove the table and the final partition -- MPP-4011: make FOR(value) work alter table rank alter partition for ('F') add default partition def1; NOTICE: CREATE TABLE will create partition "rank_1_prt_girls_2_prt_def1" for table "rank_1_prt_girls" alter table rank alter partition for ('F') truncate partition for ('2010-10-10'); ERROR: FOR expression matches DEFAULT partition "def1" of partition "girls" of relation "rank" HINT: FOR expression may only specify a non-default partition in this context. alter table rank truncate partition for ('F'); NOTICE: truncated partition "girls" for relation "rank" and its children drop table rank cascade; alter table hhh exchange partition cc with table nosuchtable with validation; ERROR: partition "cc" of relation "hhh" does not exist alter table hhh exchange partition cc with table nosuchtable without validation; ERROR: partition "cc" of relation "hhh" does not exist alter table hhh exchange partition aa with table nosuchtable with validation; ERROR: relation "nosuchtable" does not exist alter table hhh exchange partition aa with table nosuchtable without validation; ERROR: relation "nosuchtable" does not exist alter table hhh merge partition cc, partition dd; ERROR: partition "cc" of relation "hhh" does not exist alter table hhh merge partition cc, partition dd into partition ee; ERROR: partition "cc" of relation "hhh" does not exist alter table hhh merge partition aa, partition dd into partition ee; ERROR: partition "dd" of relation "hhh" does not exist alter table hhh modify partition cc add values ('a'); ERROR: partition "cc" of relation "hhh" does not exist alter table hhh modify partition cc drop values ('a'); ERROR: partition "cc" of relation "hhh" does not exist alter table hhh modify partition aa add values ('a'); ERROR: invalid use of LIST boundary specification in partition "aa" of type RANGE alter table hhh modify partition aa drop values ('a'); ERROR: invalid use of LIST boundary specification in partition "aa" of type RANGE create table mmm_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 "mmm_r1_1_prt_aa_1" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_2" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_3" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_4" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_5" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_6" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_7" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_8" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_9" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_10" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_11" for table "mmm_r1" NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_aa_12" for table "mmm_r1" create table mmm_l1 (a char(1), b char(1), d char(3)) distributed by (a) partition by list (b) ( partition aa values ('a', 'b', 'c'), partition bb values ('d', 'e', 'f'), partition cc values ('g', 'h', 'i') ); NOTICE: CREATE TABLE will create partition "mmm_l1_1_prt_aa" for table "mmm_l1" NOTICE: CREATE TABLE will create partition "mmm_l1_1_prt_bb" for table "mmm_l1" NOTICE: CREATE TABLE will create partition "mmm_l1_1_prt_cc" for table "mmm_l1" alter table mmm_r1 drop partition for ('2007-03-01'); NOTICE: dropped partition "aa_3" for relation "mmm_r1" -- ok alter table mmm_r1 add partition bb START ('2007-03-03') END ('2007-03-20'); NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_bb" for table "mmm_r1" -- fail alter table mmm_r1 modify partition for (rank(-55)) start ('2007-03-02'); ERROR: partition for rank -55 of relation "mmm_r1" does not exist alter table mmm_r1 modify partition for ('2001-01-01') start ('2007-03-02'); ERROR: partition for value ('2001-01-01') of relation "mmm_r1" does not exist alter table mmm_r1 modify partition bb start ('2006-03-02'); ERROR: cannot MODIFY RANGE partition "bb" for relation "mmm_r1" -- would overlap existing partition "aa_2" alter table mmm_r1 modify partition bb start ('2011-03-02'); ERROR: START greater than END for partition "bb" alter table mmm_r1 modify partition bb end ('2006-03-02'); ERROR: START greater than END for partition "bb" alter table mmm_r1 modify partition bb end ('2011-03-02'); ERROR: cannot MODIFY RANGE partition "bb" for relation "mmm_r1" -- would overlap existing partition "aa_4" alter table mmm_r1 modify partition bb add values ('2011-03-02'); ERROR: invalid use of LIST boundary specification in partition "bb" of type RANGE alter table mmm_r1 modify partition bb drop values ('2011-03-02'); ERROR: invalid use of LIST boundary specification in partition "bb" of type RANGE --ok alter table mmm_r1 modify partition bb START ('2007-03-02') END ('2007-03-22'); alter table mmm_r1 modify partition bb START ('2007-03-01') END ('2007-03-31'); alter table mmm_r1 modify partition bb START ('2007-03-02') END ('2007-03-22'); -- with default alter table mmm_r1 add default partition def1; NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_def1" for table "mmm_r1" -- now fail alter table mmm_r1 modify partition bb START ('2007-03-01') END ('2007-03-31'); ERROR: cannot MODIFY RANGE partition "bb" for relation "mmm_r1" to extend range -- would overlap DEFAULT partition "def1" HINT: need to SPLIT partition "def1" -- still ok to reduce range alter table mmm_r1 modify partition bb START ('2007-03-09') END ('2007-03-10'); -- fail alter table mmm_l1 modify partition for (rank(1)) drop values ('k'); ERROR: cannot find partition by RANK -- relation "mmm_l1" is LIST partitioned alter table mmm_l1 modify partition for ('j') drop values ('k'); ERROR: partition for value ('j') of relation "mmm_l1" does not exist alter table mmm_l1 modify partition for ('a') drop values ('k'); ERROR: cannot MODIFY LIST partition "aa" for relation "mmm_l1" -- DROP value not found alter table mmm_l1 modify partition for ('a') drop values ('e'); ERROR: cannot MODIFY LIST partition "aa" for relation "mmm_l1" -- found DROP value in partition "bb" alter table mmm_l1 modify partition for ('a') add values ('e'); ERROR: cannot MODIFY LIST partition "aa" for relation "mmm_l1" -- would overlap existing partition "bb" alter table mmm_l1 modify partition for ('a') START ('2007-03-09') ; ERROR: invalid use of RANGE boundary specification in partition "aa" of type LIST --ok alter table mmm_l1 modify partition for ('a') drop values ('b'); alter table mmm_l1 modify partition for ('a') add values ('z'); -- with default alter table mmm_l1 add default partition def1; NOTICE: CREATE TABLE will create partition "mmm_l1_1_prt_def1" for table "mmm_l1" -- ok alter table mmm_l1 modify partition for ('a') drop values ('c'); -- now fail alter table mmm_l1 modify partition for ('a') add values ('y'); ERROR: cannot MODIFY LIST partition "aa" for relation "mmm_l1" to ADD values -- would overlap DEFAULT partition "def1" HINT: need to SPLIT partition "def1" -- XXX XXX: add some data drop table mmm_r1 cascade; drop table mmm_l1 cascade; alter table hhh rename partition cc to aa; ERROR: partition "cc" of relation "hhh" does not exist alter table hhh rename partition aa to aa; ERROR: partition "aa" of relation "hhh" already exists alter table hhh rename partition aa to "funky fresh"; alter table hhh rename partition "funky fresh" to aa; -- use FOR PARTITION VALUE (with implicate date conversion) alter table hhh rename partition for ('2007-01-01') to "funky fresh"; NOTICE: renamed partition "aa" to "funky fresh" for relation "hhh" alter table hhh rename partition for ('2007-01-01') to aa; NOTICE: renamed partition "funky fresh" to "aa" for relation "hhh" alter table hhh set subpartition template (); ERROR: relation "hhh" does not have a level 1 subpartition template specification alter table hhh split partition cc at ('a'); ERROR: partition "cc" of relation "hhh" does not exist alter table hhh split partition cc at ('a') into (partition gg, partition hh); ERROR: partition "cc" of relation "hhh" does not exist alter table hhh split partition aa at ('a'); ERROR: invalid input syntax for type date: "a" alter table hhh truncate partition cc; ERROR: partition "cc" of relation "hhh" does not exist alter table hhh truncate partition aa; insert into hhh values('a', date '2007-01-02', 'b'); insert into hhh values('a', date '2007-02-01', 'b'); insert into hhh values('a', date '2007-03-01', 'b'); insert into hhh values('a', date '2007-04-01', 'b'); insert into hhh values('a', date '2007-05-01', 'b'); insert into hhh values('a', date '2007-06-01', 'b'); insert into hhh values('a', date '2007-07-01', 'b'); insert into hhh values('a', date '2007-08-01', 'b'); insert into hhh values('a', date '2007-09-01', 'b'); insert into hhh values('a', date '2007-10-01', 'b'); insert into hhh values('a', date '2007-11-01', 'b'); insert into hhh values('a', date '2007-12-01', 'b'); insert into hhh values('a', date '2008-01-02', 'b'); insert into hhh values('a', date '2008-02-01', 'b'); insert into hhh values('a', date '2008-03-01', 'b'); insert into hhh values('a', date '2008-04-01', 'b'); insert into hhh values('a', date '2008-05-01', 'b'); insert into hhh values('a', date '2008-06-01', 'b'); insert into hhh values('a', date '2008-07-01', 'b'); insert into hhh values('a', date '2008-08-01', 'b'); insert into hhh values('a', date '2008-09-01', 'b'); insert into hhh values('a', date '2008-10-01', 'b'); insert into hhh values('a', date '2008-11-01', 'b'); insert into hhh values('a', date '2008-12-01', 'b'); select * from hhh; a | b | d ---+------------+----- a | 01-02-2007 | b a | 02-01-2007 | b a | 03-01-2007 | b a | 04-01-2007 | b a | 05-01-2007 | b a | 06-01-2007 | b a | 07-01-2007 | b a | 08-01-2007 | b a | 09-01-2007 | b a | 10-01-2007 | b a | 11-01-2007 | b a | 12-01-2007 | b a | 01-02-2008 | b a | 02-01-2008 | b a | 03-01-2008 | b a | 04-01-2008 | b a | 05-01-2008 | b a | 06-01-2008 | b a | 07-01-2008 | b a | 08-01-2008 | b a | 09-01-2008 | b a | 10-01-2008 | b a | 11-01-2008 | b a | 12-01-2008 | b (24 rows) alter table hhh truncate partition aa; select * from hhh; a | b | d ---+------------+----- a | 01-02-2008 | b a | 02-01-2008 | b a | 03-01-2008 | b a | 04-01-2008 | b a | 05-01-2008 | b a | 06-01-2008 | b a | 07-01-2008 | b a | 08-01-2008 | b a | 09-01-2008 | b a | 10-01-2008 | b a | 11-01-2008 | b a | 12-01-2008 | b (12 rows) alter table hhh truncate partition bb; select * from hhh; a | b | d ---+---+--- (0 rows) insert into hhh values('a', date '2007-01-02', 'b'); insert into hhh values('a', date '2007-02-01', 'b'); insert into hhh values('a', date '2007-03-01', 'b'); insert into hhh values('a', date '2007-04-01', 'b'); insert into hhh values('a', date '2007-05-01', 'b'); insert into hhh values('a', date '2007-06-01', 'b'); insert into hhh values('a', date '2007-07-01', 'b'); insert into hhh values('a', date '2007-08-01', 'b'); insert into hhh values('a', date '2007-09-01', 'b'); insert into hhh values('a', date '2007-10-01', 'b'); insert into hhh values('a', date '2007-11-01', 'b'); insert into hhh values('a', date '2007-12-01', 'b'); insert into hhh values('a', date '2008-01-02', 'b'); insert into hhh values('a', date '2008-02-01', 'b'); insert into hhh values('a', date '2008-03-01', 'b'); insert into hhh values('a', date '2008-04-01', 'b'); insert into hhh values('a', date '2008-05-01', 'b'); insert into hhh values('a', date '2008-06-01', 'b'); insert into hhh values('a', date '2008-07-01', 'b'); insert into hhh values('a', date '2008-08-01', 'b'); insert into hhh values('a', date '2008-09-01', 'b'); insert into hhh values('a', date '2008-10-01', 'b'); insert into hhh values('a', date '2008-11-01', 'b'); insert into hhh values('a', date '2008-12-01', 'b'); select * from hhh; a | b | d ---+------------+----- a | 01-02-2007 | b a | 02-01-2007 | b a | 03-01-2007 | b a | 04-01-2007 | b a | 05-01-2007 | b a | 06-01-2007 | b a | 07-01-2007 | b a | 08-01-2007 | b a | 09-01-2007 | b a | 10-01-2007 | b a | 11-01-2007 | b a | 12-01-2007 | b a | 01-02-2008 | b a | 02-01-2008 | b a | 03-01-2008 | b a | 04-01-2008 | b a | 05-01-2008 | b a | 06-01-2008 | b a | 07-01-2008 | b a | 08-01-2008 | b a | 09-01-2008 | b a | 10-01-2008 | b a | 11-01-2008 | b a | 12-01-2008 | b (24 rows) -- truncate child partitions recursively truncate table hhh; select * from hhh; a | b | d ---+---+--- (0 rows) drop table hhh cascade; -- default partitions -- hash partitions cannot have default partitions create table jjj (aa int, bb int) partition by hash(bb) (partition j1, partition j2, default partition j3); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. ERROR: invalid use of DEFAULT partition for partition "j3" of type HASH LINE 3: (partition j1, partition j2, default partition j3); ^ -- 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'), default partition j3 end (date '2010-01-01')); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. ERROR: invalid use of boundary specification for DEFAULT partition "j3" LINE 5: default partition j3 end (date '2010-01-01')); ^ -- more than 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, default partition j4); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. ERROR: multiple default partitions are not allowed LINE 6: default partition j4); ^ -- check default create table foz (i int, d date) distributed by (i) partition by range (d) ( default partition dsf, partition foo start (date '2001-01-01') end (date '2005-01-01') every(interval '1 year') ); NOTICE: CREATE TABLE will create partition "foz_1_prt_dsf" for table "foz" NOTICE: CREATE TABLE will create partition "foz_1_prt_foo_1" for table "foz" NOTICE: CREATE TABLE will create partition "foz_1_prt_foo_2" for table "foz" NOTICE: CREATE TABLE will create partition "foz_1_prt_foo_3" for table "foz" NOTICE: CREATE TABLE will create partition "foz_1_prt_foo_4" for table "foz" insert into foz values(1, '2003-04-01'); insert into foz values(2, '2010-04-01'); select * from foz; i | d ---+------------ 1 | 04-01-2003 2 | 04-01-2010 (2 rows) select * from foz_1_prt_dsf; i | d ---+------------ 2 | 04-01-2010 (1 row) drop table foz cascade; -- check for out of order partition definitions. We should order these correctly -- and determine the appropriate boundaries. create table d (i int, j int) distributed by (i) partition by range(j) ( start (10), start(5), start(50) end(60)); NOTICE: CREATE TABLE will create partition "d_1_prt_1" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_2" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_3" for table "d" insert into d values(1, 5); insert into d values(1, 10); insert into d values(1, 11); insert into d values(1, 55); insert into d values(1, 70); ERROR: no partition for partitioning key (seg0 xzhangmac:40000 pid=94563) select * from d; i | j ---+---- 1 | 5 1 | 10 1 | 11 1 | 55 (4 rows) select * from d_1_prt_1; i | j ---+--- 1 | 5 (1 row) select * from d_1_prt_2; i | j ---+---- 1 | 10 1 | 11 (2 rows) select * from d_1_prt_3; i | j ---+---- 1 | 55 (1 row) drop table d cascade; -- check for NULL support -- hash create table d (i int, j int) partition by hash(j) partitions 4; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "d_1_prt_1" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_2" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_3" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_4" for table "d" insert into d values(1, NULL); insert into d values(NULL, NULL); drop table d cascade; -- list create table d (i int, j int) partition by list(j) (partition a values(1, 2, NULL), partition b values(3, 4) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "d_1_prt_a" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_b" for table "d" insert into d values(1, 1); insert into d values(1, 2); insert into d values(1, NULL); insert into d values(1, 3); insert into d values(1, 4); select * from d_1_prt_a; i | j ---+--- 1 | 1 1 | 2 1 | (3 rows) select * from d_1_prt_b; i | j ---+--- 1 | 3 1 | 4 (2 rows) drop table d cascade; --range -- Reject NULL values create table d (i int, j int) partition by range(j) (partition a start (1) end(10), partition b start(11) end(20)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "d_1_prt_a" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_b" for table "d" insert into d values (1, 1); insert into d values (1, 2); insert into d values (1, NULL); ERROR: no partition for partitioning key (seg0 xzhangmac:40000 pid=94563) drop table d cascade; -- allow NULLs into the default partition create table d (i int, j int) partition by range(j) (partition a start (1) end(10), partition b start(11) end(20), default partition abc); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "d_1_prt_abc" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_a" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_b" for table "d" insert into d values (1, 1); insert into d values (1, 2); insert into d values (1, NULL); select * from d_1_prt_abc; i | j ---+--- 1 | (1 row) drop table d cascade; -- multicolumn list support create table d (a int, b int, c int) distributed by (a) partition by list(b, c) (partition a values(('1', '2'), ('3', '4')), partition b values(('100', '20')), partition c values(('1000', '1001'), ('1001', '1002'), ('1003', '1004'))); NOTICE: CREATE TABLE will create partition "d_1_prt_a" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_b" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_c" for table "d" insert into d values(1, 1, 2); insert into d values(1, 3, 4); insert into d values(1, 100, 20); insert into d values(1, 100, 2000); ERROR: no partition for partitioning key (seg0 xzhangmac:40000 pid=94563) insert into d values(1, '1000', '1001'), (1, '1001', '1002'), (1, '1003', '1004'); insert into d values(1, 100, NULL); ERROR: no partition for partitioning key (seg0 xzhangmac:40000 pid=94563) select * from d_1_prt_a; a | b | c ---+---+--- 1 | 1 | 2 1 | 3 | 4 (2 rows) select * from d_1_prt_b; a | b | c ---+-----+---- 1 | 100 | 20 (1 row) select * from d_1_prt_c; a | b | c ---+------+------ 1 | 1000 | 1001 1 | 1001 | 1002 1 | 1003 | 1004 (3 rows) drop table d cascade; -- test multi value range partitioning create table b (i int, j date) distributed by (i) partition by range (i, j) (start(1, '2008-01-01') end (10, '2009-01-01'), start(1, '2009-01-01') end(15, '2010-01-01'), start(15, '2010-01-01') end (30, '2011-01-01'), start(1, '2011-01-01') end (100, '2012-01-01') ); ERROR: too many columns for RANGE partition -- only one column is allowed. LINE 2: partition by range (i, j) ^ -- should work insert into b values(1, '2008-06-11'); ERROR: relation "b" does not exist LINE 1: insert into b values(1, '2008-06-11'); ^ insert into b values(11, '2009-08-24'); ERROR: relation "b" does not exist LINE 1: insert into b values(11, '2009-08-24'); ^ insert into b values(25, '2010-01-22'); ERROR: relation "b" does not exist LINE 1: insert into b values(25, '2010-01-22'); ^ insert into b values(90, '2011-05-04'); ERROR: relation "b" does not exist LINE 1: insert into b values(90, '2011-05-04'); ^ -- shouldn't work insert into b values(1, '2019-01-01'); ERROR: relation "b" does not exist LINE 1: insert into b values(1, '2019-01-01'); ^ insert into b values(91, '2008-05-05'); ERROR: relation "b" does not exist LINE 1: insert into b values(91, '2008-05-05'); ^ select * from b_1_prt_1; ERROR: relation "b_1_prt_1" does not exist LINE 1: select * from b_1_prt_1; ^ select * from b_1_prt_2; ERROR: relation "b_1_prt_2" does not exist LINE 1: select * from b_1_prt_2; ^ select * from b_1_prt_3; ERROR: relation "b_1_prt_3" does not exist LINE 1: select * from b_1_prt_3; ^ select * from b_1_prt_4; ERROR: relation "b_1_prt_4" does not exist LINE 1: select * from b_1_prt_4; ^ drop table b; ERROR: table "b" does not exist -- try some different combinations create table b (i int, n numeric(20, 2), t timestamp, s text) distributed by (i) partition by range(n, t, s) ( start(2000.99, '2007-01-01 00:00:00', 'AAA') end (4000.95, '2007-02-02 15:00:00', 'BBB'), start(2000.99, '2007-01-01 00:00:00', 'BBB') end (4000.95, '2007-02-02 16:00:00', 'CCC'), start(4000.95, '2007-01-01 00:00:00', 'AAA') end (7000.95, '2007-02-02 15:00:00', 'BBB') ); ERROR: too many columns for RANGE partition -- only one column is allowed. LINE 3: partition by range(n, t, s) ^ -- should work insert into b values(1, 2000.99, '2007-01-01 00:00:00', 'AAA'); ERROR: relation "b" does not exist LINE 1: insert into b values(1, 2000.99, '2007-01-01 00:00:00', 'AAA... ^ insert into b values(2, 2000.99, '2007-01-01 00:00:00', 'BBB'); ERROR: relation "b" does not exist LINE 1: insert into b values(2, 2000.99, '2007-01-01 00:00:00', 'BBB... ^ insert into b values(3, 4000.95, '2007-01-01 00:00:00', 'AAA'); ERROR: relation "b" does not exist LINE 1: insert into b values(3, 4000.95, '2007-01-01 00:00:00', 'AAA... ^ insert into b values(6, 3000, '2007-02-02 15:30:00', 'BBC'); ERROR: relation "b" does not exist LINE 1: insert into b values(6, 3000, '2007-02-02 15:30:00', 'BBC'); ^ insert into b values(6, 3000, '2007-02-02 15:30:00', 'CC'); ERROR: relation "b" does not exist LINE 1: insert into b values(6, 3000, '2007-02-02 15:30:00', 'CC'); ^ insert into b values(6, 3000, '2007-02-02 16:00:00'::timestamp - '1 second'::interval, 'BBZZZZZZZZZZ'); ERROR: relation "b" does not exist LINE 1: insert into b values(6, 3000, '2007-02-02 16:00:00'::timesta... ^ -- should fail insert into b values(6, 3000, '2007-02-02 15:30:00', 'CCCCCCC'); ERROR: relation "b" does not exist LINE 1: insert into b values(6, 3000, '2007-02-02 15:30:00', 'CCCCCC... ^ insert into b values(4, 5000, '2007-01-01 12:00:00', 'BCC'); ERROR: relation "b" does not exist LINE 1: insert into b values(4, 5000, '2007-01-01 12:00:00', 'BCC'); ^ insert into b values(5, 8000, '2007-01-01 12:00:00', 'ZZZ'); ERROR: relation "b" does not exist LINE 1: insert into b values(5, 8000, '2007-01-01 12:00:00', 'ZZZ'); ^ insert into b values(6, 3000, '2007-02-02 16:00:00', 'ABZZZZZZZZZZ'); ERROR: relation "b" does not exist LINE 1: insert into b values(6, 3000, '2007-02-02 16:00:00', 'ABZZZZ... ^ insert into b values(6, 1000, '2007-02-02 16:00:00', 'ABZZZZZZZZZZ'); ERROR: relation "b" does not exist LINE 1: insert into b values(6, 1000, '2007-02-02 16:00:00', 'ABZZZZ... ^ insert into b values(6, 3000, '2006-02-02 16:00:00', 'ABZZZZZZZZZZ'); ERROR: relation "b" does not exist LINE 1: insert into b values(6, 3000, '2006-02-02 16:00:00', 'ABZZZZ... ^ insert into b values(6, 3000, '2007-02-02 00:00:00', 'A'); ERROR: relation "b" does not exist LINE 1: insert into b values(6, 3000, '2007-02-02 00:00:00', 'A'); ^ -- NULL tests insert into b default values; ERROR: relation "b" does not exist LINE 1: insert into b default values; ^ insert into b values(6, 3000, '2007-01-01 12:00:00', NULL); ERROR: relation "b" does not exist LINE 1: insert into b values(6, 3000, '2007-01-01 12:00:00', NULL); ^ drop table b; ERROR: table "b" does not exist -- check that we detect subpartitions partitioning a column that is already -- a partitioning target create table a (i int, b int) distributed by (i) partition by range (i) subpartition by hash(b) subpartitions 3, subpartition by hash(b) subpartitions 2 (start(1) end(100), start(100) end(1000) ); NOTICE: CREATE TABLE will create partition "a_1_prt_1" for table "a" NOTICE: CREATE TABLE will create partition "a_1_prt_1_2_prt_1" for table "a_1_prt_1" NOTICE: CREATE TABLE will create partition "a_1_prt_1_2_prt_1_3_prt_1" for table "a_1_prt_1_2_prt_1" NOTICE: CREATE TABLE will create partition "a_1_prt_1_2_prt_1_3_prt_2" for table "a_1_prt_1_2_prt_1" NOTICE: CREATE TABLE will create partition "a_1_prt_1_2_prt_2" for table "a_1_prt_1" NOTICE: CREATE TABLE will create partition "a_1_prt_1_2_prt_2_3_prt_1" for table "a_1_prt_1_2_prt_2" NOTICE: CREATE TABLE will create partition "a_1_prt_1_2_prt_2_3_prt_2" for table "a_1_prt_1_2_prt_2" NOTICE: CREATE TABLE will create partition "a_1_prt_1_2_prt_3" for table "a_1_prt_1" NOTICE: CREATE TABLE will create partition "a_1_prt_1_2_prt_3_3_prt_1" for table "a_1_prt_1_2_prt_3" NOTICE: CREATE TABLE will create partition "a_1_prt_1_2_prt_3_3_prt_2" for table "a_1_prt_1_2_prt_3" NOTICE: CREATE TABLE will create partition "a_1_prt_2" for table "a" NOTICE: CREATE TABLE will create partition "a_1_prt_2_2_prt_1" for table "a_1_prt_2" NOTICE: CREATE TABLE will create partition "a_1_prt_2_2_prt_1_3_prt_1" for table "a_1_prt_2_2_prt_1" NOTICE: CREATE TABLE will create partition "a_1_prt_2_2_prt_1_3_prt_2" for table "a_1_prt_2_2_prt_1" NOTICE: CREATE TABLE will create partition "a_1_prt_2_2_prt_2" for table "a_1_prt_2" NOTICE: CREATE TABLE will create partition "a_1_prt_2_2_prt_2_3_prt_1" for table "a_1_prt_2_2_prt_2" NOTICE: CREATE TABLE will create partition "a_1_prt_2_2_prt_2_3_prt_2" for table "a_1_prt_2_2_prt_2" NOTICE: CREATE TABLE will create partition "a_1_prt_2_2_prt_3" for table "a_1_prt_2" NOTICE: CREATE TABLE will create partition "a_1_prt_2_2_prt_3_3_prt_1" for table "a_1_prt_2_2_prt_3" NOTICE: CREATE TABLE will create partition "a_1_prt_2_2_prt_3_3_prt_2" for table "a_1_prt_2_2_prt_3" -- MPP-3988: allow same column in multiple partitioning keys at -- different levels -- so this is legal again... drop table if exists a; -- TEST: make sure GPOPT (aka pivotal query optimizer) fall back to legacy query optimizer -- for queries with partition elimination over FULL OUTER JOIN -- between partitioned tables. -- SETUP -- start_ignore drop table if exists s1; NOTICE: table "s1" does not exist, skipping drop table if exists s2; NOTICE: table "s2" does not exist, skipping -- setup two partitioned tables s1 and s2 create table s1 (d1 int, p1 int) distributed by (d1) partition by list (p1) ( values (0), values (1)); NOTICE: CREATE TABLE will create partition "s1_1_prt_1" for table "s1" NOTICE: CREATE TABLE will create partition "s1_1_prt_2" for table "s1" create table s2 (d2 int, p2 int) distributed by (d2) partition by list (p2) ( values (0), values (1)); NOTICE: CREATE TABLE will create partition "s2_1_prt_1" for table "s2" NOTICE: CREATE TABLE will create partition "s2_1_prt_2" for table "s2" -- end_ignore -- VERIFY -- expect GPOPT fall back to legacy query optimizer -- since GPOPT don't support partition elimination through full outer joins select * from s1 full outer join s2 on s1.d1 = s2.d2 and s1.p1 = s2.p2 where s1.p1 = 1; d1 | p1 | d2 | p2 ----+----+----+---- (0 rows) -- CLEANUP -- start_ignore drop table if exists s1; drop table if exists s2; -- end_ignore create table mpp_2914A(id int, buyDate date, kind char(1)) DISTRIBUTED BY (id) partition by list (kind) subpartition by range(buyDate) 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 auction values('a','A'), partition buyItNow values('b', 'B'), default partition catchall ); NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_auction" for table "mpp_2914a" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_auction_2_prt_1" for table "mpp_2914a_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_auction_2_prt_2" for table "mpp_2914a_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_auction_2_prt_3" for table "mpp_2914a_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_auction_2_prt_4" for table "mpp_2914a_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_auction_2_prt_5" for table "mpp_2914a_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_buyitnow" for table "mpp_2914a" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_buyitnow_2_prt_1" for table "mpp_2914a_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_buyitnow_2_prt_2" for table "mpp_2914a_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_buyitnow_2_prt_3" for table "mpp_2914a_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_buyitnow_2_prt_4" for table "mpp_2914a_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_buyitnow_2_prt_5" for table "mpp_2914a_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_catchall" for table "mpp_2914a" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_catchall_2_prt_1" for table "mpp_2914a_1_prt_catchall" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_catchall_2_prt_2" for table "mpp_2914a_1_prt_catchall" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_catchall_2_prt_3" for table "mpp_2914a_1_prt_catchall" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_catchall_2_prt_4" for table "mpp_2914a_1_prt_catchall" NOTICE: CREATE TABLE will create partition "mpp_2914a_1_prt_catchall_2_prt_5" for table "mpp_2914a_1_prt_catchall" select count(*) from mpp_2914A; count ------- 0 (1 row) \d mpp_2914a* Table "public.mpp_2914a" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Number of child tables: 3 (Use \d+ to list them.) Distributed by: (id) Partition by: (kind) Table "public.mpp_2914a_1_prt_auction" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914a Number of child tables: 5 (Use \d+ to list them.) Distributed by: (id) Partition by: (buydate) Table "public.mpp_2914a_1_prt_auction_2_prt_1" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_auction_2_prt_1_check" CHECK (buydate >= '01-01-2001'::date AND buydate < '01-01-2002'::date) "mpp_2914a_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914a_1_prt_auction Distributed by: (id) Table "public.mpp_2914a_1_prt_auction_2_prt_2" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_auction_2_prt_2_check" CHECK (buydate >= '01-01-2002'::date AND buydate < '01-01-2003'::date) "mpp_2914a_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914a_1_prt_auction Distributed by: (id) Table "public.mpp_2914a_1_prt_auction_2_prt_3" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_auction_2_prt_3_check" CHECK (buydate >= '01-01-2003'::date AND buydate < '01-01-2004'::date) "mpp_2914a_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914a_1_prt_auction Distributed by: (id) Table "public.mpp_2914a_1_prt_auction_2_prt_4" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_auction_2_prt_4_check" CHECK (buydate >= '01-01-2004'::date AND buydate < '01-01-2005'::date) "mpp_2914a_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914a_1_prt_auction Distributed by: (id) Table "public.mpp_2914a_1_prt_auction_2_prt_5" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_auction_2_prt_5_check" CHECK (buydate >= '01-01-2005'::date) "mpp_2914a_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914a_1_prt_auction Distributed by: (id) Table "public.mpp_2914a_1_prt_buyitnow" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914a Number of child tables: 5 (Use \d+ to list them.) Distributed by: (id) Partition by: (buydate) Table "public.mpp_2914a_1_prt_buyitnow_2_prt_1" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_buyitnow_2_prt_1_check" CHECK (buydate >= '01-01-2001'::date AND buydate < '01-01-2002'::date) "mpp_2914a_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914a_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914a_1_prt_buyitnow_2_prt_2" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_buyitnow_2_prt_2_check" CHECK (buydate >= '01-01-2002'::date AND buydate < '01-01-2003'::date) "mpp_2914a_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914a_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914a_1_prt_buyitnow_2_prt_3" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_buyitnow_2_prt_3_check" CHECK (buydate >= '01-01-2003'::date AND buydate < '01-01-2004'::date) "mpp_2914a_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914a_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914a_1_prt_buyitnow_2_prt_4" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_buyitnow_2_prt_4_check" CHECK (buydate >= '01-01-2004'::date AND buydate < '01-01-2005'::date) "mpp_2914a_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914a_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914a_1_prt_buyitnow_2_prt_5" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_buyitnow_2_prt_5_check" CHECK (buydate >= '01-01-2005'::date) "mpp_2914a_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914a_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914a_1_prt_catchall" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Inherits: mpp_2914a Number of child tables: 5 (Use \d+ to list them.) Distributed by: (id) Partition by: (buydate) Table "public.mpp_2914a_1_prt_catchall_2_prt_1" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_catchall_2_prt_1_check" CHECK (buydate >= '01-01-2001'::date AND buydate < '01-01-2002'::date) Inherits: mpp_2914a_1_prt_catchall Distributed by: (id) Table "public.mpp_2914a_1_prt_catchall_2_prt_2" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_catchall_2_prt_2_check" CHECK (buydate >= '01-01-2002'::date AND buydate < '01-01-2003'::date) Inherits: mpp_2914a_1_prt_catchall Distributed by: (id) Table "public.mpp_2914a_1_prt_catchall_2_prt_3" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_catchall_2_prt_3_check" CHECK (buydate >= '01-01-2003'::date AND buydate < '01-01-2004'::date) Inherits: mpp_2914a_1_prt_catchall Distributed by: (id) Table "public.mpp_2914a_1_prt_catchall_2_prt_4" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_catchall_2_prt_4_check" CHECK (buydate >= '01-01-2004'::date AND buydate < '01-01-2005'::date) Inherits: mpp_2914a_1_prt_catchall Distributed by: (id) Table "public.mpp_2914a_1_prt_catchall_2_prt_5" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914a_1_prt_catchall_2_prt_5_check" CHECK (buydate >= '01-01-2005'::date) Inherits: mpp_2914a_1_prt_catchall Distributed by: (id) create table mpp_2914B(id int, buyDate date, kind char(1)) DISTRIBUTED BY (id) partition by list (kind) subpartition by range(buyDate) ( partition auction values('a','A') ( subpartition y2001 start (date '2001-01-01'), subpartition y2002 start (date '2002-01-01'), subpartition y2003 start (date '2003-01-01'), subpartition y2004 start (date '2004-01-01'), subpartition y2005 start (date '2005-01-01') ), partition buyitnow values('b','B') ( subpartition y2001 start (date '2001-01-01'), subpartition y2002 start (date '2002-01-01'), subpartition y2003 start (date '2003-01-01'), subpartition y2004 start (date '2004-01-01'), subpartition y2005 start (date '2005-01-01') ), default partition catchAll ( subpartition y2001 start (date '2001-01-01'), subpartition y2002 start (date '2002-01-01'), subpartition y2003 start (date '2003-01-01'), subpartition y2004 start (date '2004-01-01'), subpartition y2005 start (date '2005-01-01') ) ); NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_auction" for table "mpp_2914b" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_auction_2_prt_y2001" for table "mpp_2914b_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_auction_2_prt_y2002" for table "mpp_2914b_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_auction_2_prt_y2003" for table "mpp_2914b_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_auction_2_prt_y2004" for table "mpp_2914b_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_auction_2_prt_y2005" for table "mpp_2914b_1_prt_auction" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_buyitnow" for table "mpp_2914b" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_buyitnow_2_prt_y2001" for table "mpp_2914b_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_buyitnow_2_prt_y2002" for table "mpp_2914b_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_buyitnow_2_prt_y2003" for table "mpp_2914b_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_buyitnow_2_prt_y2004" for table "mpp_2914b_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_buyitnow_2_prt_y2005" for table "mpp_2914b_1_prt_buyitnow" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_catchall" for table "mpp_2914b" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_catchall_2_prt_y2001" for table "mpp_2914b_1_prt_catchall" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_catchall_2_prt_y2002" for table "mpp_2914b_1_prt_catchall" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_catchall_2_prt_y2003" for table "mpp_2914b_1_prt_catchall" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_catchall_2_prt_y2004" for table "mpp_2914b_1_prt_catchall" NOTICE: CREATE TABLE will create partition "mpp_2914b_1_prt_catchall_2_prt_y2005" for table "mpp_2914b_1_prt_catchall" select count(*) from mpp_2914B; count ------- 0 (1 row) \d mpp_2914b* Table "public.mpp_2914b" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Number of child tables: 3 (Use \d+ to list them.) Distributed by: (id) Partition by: (kind) Table "public.mpp_2914b_1_prt_auction" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914b Number of child tables: 5 (Use \d+ to list them.) Distributed by: (id) Partition by: (buydate) Table "public.mpp_2914b_1_prt_auction_2_prt_y2001" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_auction_2_prt_y2001_check" CHECK (buydate >= '01-01-2001'::date AND buydate < '01-01-2002'::date) "mpp_2914b_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914b_1_prt_auction Distributed by: (id) Table "public.mpp_2914b_1_prt_auction_2_prt_y2002" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_auction_2_prt_y2002_check" CHECK (buydate >= '01-01-2002'::date AND buydate < '01-01-2003'::date) "mpp_2914b_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914b_1_prt_auction Distributed by: (id) Table "public.mpp_2914b_1_prt_auction_2_prt_y2003" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_auction_2_prt_y2003_check" CHECK (buydate >= '01-01-2003'::date AND buydate < '01-01-2004'::date) "mpp_2914b_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914b_1_prt_auction Distributed by: (id) Table "public.mpp_2914b_1_prt_auction_2_prt_y2004" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_auction_2_prt_y2004_check" CHECK (buydate >= '01-01-2004'::date AND buydate < '01-01-2005'::date) "mpp_2914b_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914b_1_prt_auction Distributed by: (id) Table "public.mpp_2914b_1_prt_auction_2_prt_y2005" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_auction_2_prt_y2005_check" CHECK (buydate >= '01-01-2005'::date) "mpp_2914b_1_prt_auction_check" CHECK (kind = 'a'::bpchar OR kind = 'A'::bpchar) Inherits: mpp_2914b_1_prt_auction Distributed by: (id) Table "public.mpp_2914b_1_prt_buyitnow" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914b Number of child tables: 5 (Use \d+ to list them.) Distributed by: (id) Partition by: (buydate) Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2001" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_buyitnow_2_prt_y2001_check" CHECK (buydate >= '01-01-2001'::date AND buydate < '01-01-2002'::date) "mpp_2914b_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914b_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2002" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_buyitnow_2_prt_y2002_check" CHECK (buydate >= '01-01-2002'::date AND buydate < '01-01-2003'::date) "mpp_2914b_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914b_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2003" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_buyitnow_2_prt_y2003_check" CHECK (buydate >= '01-01-2003'::date AND buydate < '01-01-2004'::date) "mpp_2914b_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914b_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2004" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_buyitnow_2_prt_y2004_check" CHECK (buydate >= '01-01-2004'::date AND buydate < '01-01-2005'::date) "mpp_2914b_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914b_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2005" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_buyitnow_2_prt_y2005_check" CHECK (buydate >= '01-01-2005'::date) "mpp_2914b_1_prt_buyitnow_check" CHECK (kind = 'b'::bpchar OR kind = 'B'::bpchar) Inherits: mpp_2914b_1_prt_buyitnow Distributed by: (id) Table "public.mpp_2914b_1_prt_catchall" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Inherits: mpp_2914b Number of child tables: 5 (Use \d+ to list them.) Distributed by: (id) Partition by: (buydate) Table "public.mpp_2914b_1_prt_catchall_2_prt_y2001" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_catchall_2_prt_y2001_check" CHECK (buydate >= '01-01-2001'::date AND buydate < '01-01-2002'::date) Inherits: mpp_2914b_1_prt_catchall Distributed by: (id) Table "public.mpp_2914b_1_prt_catchall_2_prt_y2002" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_catchall_2_prt_y2002_check" CHECK (buydate >= '01-01-2002'::date AND buydate < '01-01-2003'::date) Inherits: mpp_2914b_1_prt_catchall Distributed by: (id) Table "public.mpp_2914b_1_prt_catchall_2_prt_y2003" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_catchall_2_prt_y2003_check" CHECK (buydate >= '01-01-2003'::date AND buydate < '01-01-2004'::date) Inherits: mpp_2914b_1_prt_catchall Distributed by: (id) Table "public.mpp_2914b_1_prt_catchall_2_prt_y2004" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_catchall_2_prt_y2004_check" CHECK (buydate >= '01-01-2004'::date AND buydate < '01-01-2005'::date) Inherits: mpp_2914b_1_prt_catchall Distributed by: (id) Table "public.mpp_2914b_1_prt_catchall_2_prt_y2005" Column | Type | Modifiers ---------+--------------+----------- id | integer | buydate | date | kind | character(1) | Check constraints: "mpp_2914b_1_prt_catchall_2_prt_y2005_check" CHECK (buydate >= '01-01-2005'::date) Inherits: mpp_2914b_1_prt_catchall Distributed by: (id) drop table mpp_2914a cascade; drop table mpp_2914b cascade;