Exploring partitions in Greenplum

posted Nov 1, 2014, 10:15 AM by Sachchida Ojha
sachi=# create table monthlyexpnse 
(
category varchar(32),
expnse_date timestamptz,
amount numeric(8,2))
distributed by (category)
partition by range (expnse_date )
START (date '2013-01-01') INCLUSIVE
END  (date '2016-12-31') INCLUSIVE
EVERY (INTERVAL '1 month')
);

NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_1" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_2" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_3" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_4" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_5" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_6" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_7" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_8" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_9" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_10" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_11" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_12" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_13" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_14" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_15" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_16" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_17" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_18" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_19" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_20" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_21" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_22" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_23" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_24" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_25" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_26" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_27" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_28" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_29" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_30" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_31" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_32" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_33" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_34" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_35" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_36" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_37" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_38" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_39" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_40" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_41" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_42" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_43" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_44" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_45" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_46" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_47" for table "monthlyexpnse"
NOTICE:  CREATE TABLE will create partition "monthlyexpnse_1_prt_48" for table "monthlyexpnse"
CREATE TABLE
sachi=# 

sachi=# select tablename from pg_tables where tablename like 'monthlyexpnse_1_prt_%' order by tablename desc;
       tablename        
------------------------
 monthlyexpnse_1_prt_9
 monthlyexpnse_1_prt_8
 monthlyexpnse_1_prt_7
 monthlyexpnse_1_prt_6
 monthlyexpnse_1_prt_5
 monthlyexpnse_1_prt_48
 monthlyexpnse_1_prt_47
 monthlyexpnse_1_prt_46
 monthlyexpnse_1_prt_45
 monthlyexpnse_1_prt_44
 monthlyexpnse_1_prt_43
 monthlyexpnse_1_prt_42
 monthlyexpnse_1_prt_41
 monthlyexpnse_1_prt_40
 monthlyexpnse_1_prt_4
 monthlyexpnse_1_prt_39
 monthlyexpnse_1_prt_38
 monthlyexpnse_1_prt_37
 monthlyexpnse_1_prt_36
 monthlyexpnse_1_prt_35
 monthlyexpnse_1_prt_34
 monthlyexpnse_1_prt_33
 monthlyexpnse_1_prt_32
 monthlyexpnse_1_prt_31
 monthlyexpnse_1_prt_30
 monthlyexpnse_1_prt_3
 monthlyexpnse_1_prt_29
 monthlyexpnse_1_prt_28
 monthlyexpnse_1_prt_27
 monthlyexpnse_1_prt_26
 monthlyexpnse_1_prt_25
 monthlyexpnse_1_prt_24
 monthlyexpnse_1_prt_23
 monthlyexpnse_1_prt_22
 monthlyexpnse_1_prt_21
 monthlyexpnse_1_prt_20
 monthlyexpnse_1_prt_2
 monthlyexpnse_1_prt_19
 monthlyexpnse_1_prt_18
 monthlyexpnse_1_prt_17
 monthlyexpnse_1_prt_16
 monthlyexpnse_1_prt_15
 monthlyexpnse_1_prt_14
 monthlyexpnse_1_prt_13
 monthlyexpnse_1_prt_12
 monthlyexpnse_1_prt_11
 monthlyexpnse_1_prt_10
 monthlyexpnse_1_prt_1

sachi=# select tablename, replace(tablename,'monthlyexpnse_1_prt_','')::integer  from pg_tables where tablename like 'monthlyexpnse_1_prt_%' order by replace(tablename,'monthlyexpnse_1_prt_','')::integer asc;
       tablename        | replace 
------------------------+---------
 monthlyexpnse_1_prt_1  |       1
 monthlyexpnse_1_prt_2  |       2
 monthlyexpnse_1_prt_3  |       3
 monthlyexpnse_1_prt_4  |       4
 monthlyexpnse_1_prt_5  |       5
 monthlyexpnse_1_prt_6  |       6
 monthlyexpnse_1_prt_7  |       7
 monthlyexpnse_1_prt_8  |       8
 monthlyexpnse_1_prt_9  |       9
 monthlyexpnse_1_prt_10 |      10
 monthlyexpnse_1_prt_11 |      11
 monthlyexpnse_1_prt_12 |      12
 monthlyexpnse_1_prt_13 |      13
 monthlyexpnse_1_prt_14 |      14
 monthlyexpnse_1_prt_15 |      15
 monthlyexpnse_1_prt_16 |      16
 monthlyexpnse_1_prt_17 |      17
 monthlyexpnse_1_prt_18 |      18
 monthlyexpnse_1_prt_19 |      19
 monthlyexpnse_1_prt_20 |      20
 monthlyexpnse_1_prt_21 |      21
 monthlyexpnse_1_prt_22 |      22
 monthlyexpnse_1_prt_23 |      23
 monthlyexpnse_1_prt_24 |      24
 monthlyexpnse_1_prt_25 |      25
 monthlyexpnse_1_prt_26 |      26
 monthlyexpnse_1_prt_27 |      27
 monthlyexpnse_1_prt_28 |      28
 monthlyexpnse_1_prt_29 |      29
 monthlyexpnse_1_prt_30 |      30
 monthlyexpnse_1_prt_31 |      31
 monthlyexpnse_1_prt_32 |      32
 monthlyexpnse_1_prt_33 |      33
 monthlyexpnse_1_prt_34 |      34
 monthlyexpnse_1_prt_35 |      35
 monthlyexpnse_1_prt_36 |      36
 monthlyexpnse_1_prt_37 |      37
 monthlyexpnse_1_prt_38 |      38
 monthlyexpnse_1_prt_39 |      39
 monthlyexpnse_1_prt_40 |      40
 monthlyexpnse_1_prt_41 |      41
 monthlyexpnse_1_prt_42 |      42
 monthlyexpnse_1_prt_43 |      43
 monthlyexpnse_1_prt_44 |      44
 monthlyexpnse_1_prt_45 |      45
 monthlyexpnse_1_prt_46 |      46
 monthlyexpnse_1_prt_47 |      47
 monthlyexpnse_1_prt_48 |      48
(48 rows)

sachi=# 



Comments