sql - Multi-level partition in Greeplum -

i want make multilevel partition table in greeplum first level partition on year , second on every 7 days. when hardcode works fine.following code :

drop table if exists pd.is_it_working;  create table pd.is_it_working (   ts timestamp without time zone,   client_uname text ) (appendonly=true,    oids=false ) distributed (client_uname) partition range(ts)          subpartition range (ts)         subpartition template          (         start ('2015-06-01 00:00:00'::timestamp without time zone)inclusive          end ('2016-01-01 00:00:00'::timestamp without time zone) exclusive         every ('1 week'::interval)          (appendonly=true, orientation=parquet, compresstype=snappy, pagesize=1048576, rowgroupsize=8388608),          default subpartition outlying_week           (appendonly=true, orientation=parquet, compresstype=snappy, pagesize=1048576, rowgroupsize=8388608)         )  ( start ('2015-06-01 00:00:00'::timestamp without time zone)inclusive  end ('2016-01-01 00:00:00'::timestamp without time zone) exclusive every ('1 year'::interval)  (appendonly=true, orientation=parquet, compresstype=snappy, pagesize=1048576, rowgroupsize=8388608),  default partition outlying_year   (appendonly=true, orientation=parquet, compresstype=snappy, pagesize=1048576, rowgroupsize=8388608) ) ; alter table pd.is_it_working   owner pdugar; 

now, problem how add more partitions years(for example partition year 2016-2017) subsequent 7 days partitions? can't add partitions if have default partition. if decide split default partition, should not empty otherwise cannot partition it. if it's not empty, if split it, follow subpartition template defined above wrong new year, new subpartition template supposed there i.e let's year 2016-2017 subpartition has every 7 day in between them !

i have dynamically make new year partition(with subsequent subpartitions) table when data comes in , not able it. need partition done on 'time stamp'. there way out ?

thank you

you can't have "default" partition , add new ones -- since default may contain data should on new partitions.

in case, queries contained tuples missing data query planner uses partition elimination make query faster.

in order have functionality, need create function would:

• copy default partition new table
• drop default partition
• alter table, adding new partitions
• insert default copy
• alter table, adding default partition (if desired)

ideally, not use default partition, rather use trigger create new partition based on date range when necessary. however, greenplum doesn't allow database altering statements run on segments -- so, function is!

take @ example:

https://dba.stackexchange.com/questions/126171/how-can-i-add-multiple-partitions-to-a-greenplum-table-using-dynamic-sql