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=# |
 | |
|
|
Copyright©2012-18 http://www.greenplumdba.com/ All rights reserved.
Contact us. Email: gpdba@greenplumdba.com
First they ignore you, then they laugh at you, then they fight you, then you win! - Mahatma Gandhi
QUICK REFERENCES:
DCA | DIA | DATA DOMAIN | GPDB | COMMAND CENTER | PGADMIN3 | GEMFIRE | GREENPLUM CHORUS | PIVOTAL VRP | GPDB ADMIN UTILITIES