part_ee_ff2 | EE | 10

(8 rows)

-- some more tests to exercise tuple-routing with multi-level partitioning

create table part_gg partition of list_parted for values in ('gg') partition by range (b);

create table part_gg1 partition of part_gg for values from (unbounded) to (1);

create table part_gg2 partition of part_gg for values from (1) to (10) partition by range (b);

create table part_gg2_1 partition of part_gg2 for values from (1) to (5);

create table part_gg2_2 partition of part_gg2 for values from (5) to (10);

create table part_ee_ff3 partition of part_ee_ff for values from (20) to (30) partition by range (b);

create table part_ee_ff3_1 partition of part_ee_ff3 for values from (20) to (25);

create table part_ee_ff3_2 partition of part_ee_ff3 for values from (25) to (30);

truncate list_parted;

insert into list_parted values ('aa'), ('cc');

insert into list_parted select 'Ff', s.a from generate_series(1, 29) s(a);

insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a);

insert into list_parted (b) values (1);

select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1;

tableoid | a | min_b | max_b

---------------+----+-------+-------

part_aa_bb | aa | |

part_cc_dd | cc | |

part_ee_ff1 | Ff | 1 | 9

part_ee_ff2 | Ff | 10 | 19

part_ee_ff3_1 | Ff | 20 | 24

part_ee_ff3_2 | Ff | 25 | 29

part_gg2_1 | gg | 1 | 4

part_gg2_2 | gg | 5 | 9

part_null | | 1 | 1

(9 rows)

-- cleanup

drop table range_parted cascade;

NOTICE: drop cascades to 4 other objects

drop cascades to table part3

drop cascades to table part4

drop table list_parted cascade;

NOTICE: drop cascades to6 other objects

NOTICE: drop cascades to14 other objects

DETAIL: drop cascades to table part_aa_bb

drop cascades to table part_cc_dd

drop cascades to table part_null

drop cascades to table part_ee_ff

drop cascades to table part_ee_ff1

drop cascades to table part_ee_ff2

drop cascades to table part_ee_ff3

drop cascades to table part_ee_ff3_1

drop cascades to table part_ee_ff3_2

drop cascades to table part_gg

drop cascades to table part_gg1

drop cascades to table part_gg2

drop cascades to table part_gg2_1

drop cascades to table part_gg2_2

-- more tests for certain multi-level partitioning scenarios

create table p (a int, b int) partition by range (a, b);

create table p1 (b int, a int not null) partition by range (b);

insert into part_ee_ffvalues ('EE',10);

select tableoid::regclass,*from list_parted;

createtablepart_gg partition of list_parted forvaluesin ('gg') partition by range (b);

createtablepart_gg1 partition of part_gg forvaluesfrom (unbounded) to (1);

createtablepart_gg2 partition of part_gg forvaluesfrom (1) to (10) partition by range (b);

createtablepart_gg2_1 partition of part_gg2 forvaluesfrom (1) to (5);

createtablepart_gg2_2 partition of part_gg2 forvaluesfrom (5) to (10);

createtablepart_ee_ff3 partition of part_ee_ff forvaluesfrom (20) to (30) partition by range (b);

createtablepart_ee_ff3_1 partition of part_ee_ff3 forvaluesfrom (20) to (25);

createtablepart_ee_ff3_2 partition of part_ee_ff3 forvaluesfrom (25) to (30);

truncate list_parted;

insert into list_partedvalues ('aa'), ('cc');

insert into list_partedselect'Ff',s.afrom generate_series(1,29) s(a);

insert into list_partedselect'gg',s.afrom generate_series(1,9) s(a);

insert into list_parted (b)values (1);

select tableoid::regclass::text, a,min(b)as min_b,max(b)as max_bfrom list_partedgroup by1,2order by1;

droptable range_parted cascade;

droptable list_parted cascade;