partition1.out

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;