Examples:
CREATE TABLE rank_date_partition (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( DEFAULT PARTITION defaultDate,
PARTITION Jan08 START (date '2008-01-01') INCLUSIVE END (date '2008-02-01'::date) EXCLUSIVE EVERY (3),
PARTITION Feb08 START (date '2008-02-01') INCLUSIVE END (date '2008-03-01'::date),
PARTITION Mar08 START (date '2008-03-01') INCLUSIVE END (date '2008-04-01'::date) TABLESPACE "newSpace1",
PARTITION Apr08 START (date '2008-04-01') INCLUSIVE END (date '2008-05-01'::date),
PARTITION May08 START (date '2008-05-01') INCLUSIVE END (date '2008-06-01'::date),
PARTITION Jun08 START (date '2008-06-01') INCLUSIVE END (date '2008-07-01'::date) TABLESPACE "newSpace1",
PARTITION Jul08 START (date '2008-07-01') INCLUSIVE END (date '2008-08-01'::date) TABLESPACE pg_default,
PARTITION Aug08 START (date '2008-08-01') INCLUSIVE END (date '2008-09-01'::date) TABLESPACE pg_default,
PARTITION Sep08 START (date '2008-09-01') INCLUSIVE END (date '2008-10-01'::date) TABLESPACE "newSpace1",
PARTITION Oct08 START (date '2008-10-01') INCLUSIVE END (date '2008-11-01'::date),
PARTITION Nov08 START (date '2008-11-01') INCLUSIVE END (date '2008-12-01'::date),
PARTITION Dec08 START (date '2008-12-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE)
// ***** Numeric Partition ***** //
CREATE TABLE rank_numeric_partition (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
(START (2001) END (2008) EVERY (1), DEFAULT PARTITION extra)
// ***** List Table Partition ***** //
CREATE TABLE rank_list_partition (id int, rank int, year int, gender char(1), count int )
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
(PARTITION girls VALUES ('F'), PARTITION boys VALUES ('M'), DEFAULT PARTITION other)
// ***** Three Layer Partition ***** //
DROP TABLE rank_layered_partition cascade
GO
CREATE TABLE rank_layered_partition (id int, year int, month int, day int, gender char(1), region text) DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY LIST (gender)
SUBPARTITION BY RANGE (month)
(DEFAULT PARTITION outlying_years
(DEFAULT SUBPARTITION UG1
(DEFAULT SUBPARTITION OM1, START (4) END (6), START (7) END (9)),
VALUES ('M')
(DEFAULT SUBPARTITION OM2, START (4) END (6), START (7) END (9)),
VALUES ('F')
(DEFAULT SUBPARTITION OM3, START (4) END (6), START (7) END (9))),
START (2000) END (2002) EXCLUSIVE
(DEFAULT SUBPARTITION UG2
(DEFAULT SUBPARTITION OM4, START (10) END (12)),
VALUES ('M', 'G')
(DEFAULT SUBPARTITION OM5, START (10) END (12)),
VALUES ('F', 'L')
(DEFAULT SUBPARTITION OM6, START (10) END (12))),
START (2002) END (2004) EXCLUSIVE
(DEFAULT SUBPARTITION UG3
(DEFAULT SUBPARTITION OM7, SUBPARTITION Q1 START (1) END (3)),
VALUES ('M', 'G', 'D')
(DEFAULT SUBPARTITION OM8, SUBPARTITION Q1 START (1) END (3)),
VALUES ('F', 'L', 'H')
(DEFAULT SUBPARTITION OM9, SUBPARTITION Q1 START (1) END (3))
)
)
// ***** Templated partition ***** //
CREATE TABLE rank_template (id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY RANGE (month)
SUBPARTITION TEMPLATE (START (1) END (13) EVERY (1), DEFAULT SUBPARTITION other_months )
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (SUBPARTITION usa VALUES ('usa'), SUBPARTITION europe VALUES ('europe'), SUBPARTITION asia VALUES ('asia'), DEFAULT SUBPARTITION other_regions )
(START (2002) END (2010) EVERY (1), DEFAULT PARTITION outlying_years )
// ***** Not Able to Reverse Eng. the following table correctly ***** //
CREATE TABLE rank_TODO (i int, j int, k int, l int) WITH (APPENDONLY = TRUE, ORIENTATION=COLUMN)
PARTITION BY range(j)
SUBPARTITION BY list (k)
SUBPARTITION template(
SUBPARTITION sp1 values(1, 2, 3, 4, 5),
COLUMN i ENCODING(COMPRESSTYPE=ZLIB),
COLUMN j ENCODING(COMPRESSTYPE=QUICKLZ),
COLUMN k ENCODING(COMPRESSTYPE=ZLIB),
COLUMN l ENCODING(COMPRESSTYPE=ZLIB))
(PARTITION p1 START(1) END(10), PARTITION p2 START(10) END(20))
