Finding partition keys in Greenplum

posted Apr 28, 2017, 11:55 AM by Sachchida Ojha   [ updated Apr 28, 2017, 11:55 AM ]
CREATE TABLE sales (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 (2012) END (2013) EVERY (1),DEFAULT PARTITION outlying_years);

sachi=# CREATE TABLE sales (id int, year int, month int, day int, region text)
sachi-# DISTRIBUTED BY (id)
sachi-# PARTITION BY RANGE (year)
sachi-# SUBPARTITION BY RANGE (month)
sachi-# SUBPARTITION TEMPLATE (
sachi(# START (1) END (13) EVERY (1),
sachi(# DEFAULT SUBPARTITION other_months )
sachi-# SUBPARTITION BY LIST (region)
sachi-# SUBPARTITION TEMPLATE (
sachi(# SUBPARTITION usa VALUES ('usa'),
sachi(# SUBPARTITION europe VALUES ('europe'),
sachi(# SUBPARTITION asia VALUES ('asia'),
sachi(# DEFAULT SUBPARTITION other_regions)
sachi-# ( START (2012) END (2013) EVERY (1),
sachi(# DEFAULT PARTITION outlying_years);
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years" for table "sales"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2" for table "sales"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_other_months" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_2" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_3" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_4" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_5" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_6" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_7" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_8" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_9" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_10" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_11" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_12" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_13" for table "sales_1_prt_outlying_years"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_other_months_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_other_months"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_other_months_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_other_months"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_other_months_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_other_months"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_other_mont_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_other_months"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_2_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_2_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_2_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_2_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_3_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_3"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_3_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_3"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_3_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_3"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_3_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_3"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_4_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_4"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_4_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_4"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_4_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_4"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_4_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_4"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_5_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_5"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_5_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_5"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_5_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_5"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_5_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_5"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_6_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_6"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_6_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_6"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_6_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_6"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_6_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_6"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_7_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_7"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_7_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_7"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_7_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_7"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_7_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_7"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_8_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_8"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_8_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_8"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_8_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_8"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_8_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_8"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_9_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_9"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_9_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_9"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_9_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_9"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_9_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_9"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_10_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_10"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_10_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_10"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_10_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_10"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_10_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_10"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_11_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_11"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_11_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_11"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_11_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_11"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_11_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_11"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_12_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_12"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_12_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_12"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_12_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_12"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_12_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_12"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_13_3_prt_usa" for table "sales_1_prt_outlying_years_2_prt_13"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_13_3_prt_europe" for table "sales_1_prt_outlying_years_2_prt_13"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_13_3_prt_asia" for table "sales_1_prt_outlying_years_2_prt_13"
NOTICE: CREATE TABLE will create partition "sales_1_prt_outlying_years_2_prt_13_3_prt_other_regions" for table "sales_1_prt_outlying_years_2_prt_13"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_months" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_2" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_3" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_4" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_5" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_6" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_7" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_8" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_9" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_10" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_11" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_12" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_13" for table "sales_1_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_months_3_prt_usa" for table "sales_1_prt_2_2_prt_other_months"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_months_3_prt_europe" for table "sales_1_prt_2_2_prt_other_months"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_months_3_prt_asia" for table "sales_1_prt_2_2_prt_other_months"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_months_3_prt_other_regions" for table "sales_1_prt_2_2_prt_other_months"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_2_3_prt_usa" for table "sales_1_prt_2_2_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_2_3_prt_europe" for table "sales_1_prt_2_2_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_2_3_prt_asia" for table "sales_1_prt_2_2_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_2_3_prt_other_regions" for table "sales_1_prt_2_2_prt_2"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_3_3_prt_usa" for table "sales_1_prt_2_2_prt_3"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_3_3_prt_europe" for table "sales_1_prt_2_2_prt_3"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_3_3_prt_asia" for table "sales_1_prt_2_2_prt_3"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_3_3_prt_other_regions" for table "sales_1_prt_2_2_prt_3"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_4_3_prt_usa" for table "sales_1_prt_2_2_prt_4"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_4_3_prt_europe" for table "sales_1_prt_2_2_prt_4"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_4_3_prt_asia" for table "sales_1_prt_2_2_prt_4"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_4_3_prt_other_regions" for table "sales_1_prt_2_2_prt_4"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_5_3_prt_usa" for table "sales_1_prt_2_2_prt_5"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_5_3_prt_europe" for table "sales_1_prt_2_2_prt_5"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_5_3_prt_asia" for table "sales_1_prt_2_2_prt_5"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_5_3_prt_other_regions" for table "sales_1_prt_2_2_prt_5"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_6_3_prt_usa" for table "sales_1_prt_2_2_prt_6"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_6_3_prt_europe" for table "sales_1_prt_2_2_prt_6"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_6_3_prt_asia" for table "sales_1_prt_2_2_prt_6"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_6_3_prt_other_regions" for table "sales_1_prt_2_2_prt_6"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_7_3_prt_usa" for table "sales_1_prt_2_2_prt_7"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_7_3_prt_europe" for table "sales_1_prt_2_2_prt_7"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_7_3_prt_asia" for table "sales_1_prt_2_2_prt_7"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_7_3_prt_other_regions" for table "sales_1_prt_2_2_prt_7"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_8_3_prt_usa" for table "sales_1_prt_2_2_prt_8"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_8_3_prt_europe" for table "sales_1_prt_2_2_prt_8"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_8_3_prt_asia" for table "sales_1_prt_2_2_prt_8"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_8_3_prt_other_regions" for table "sales_1_prt_2_2_prt_8"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_9_3_prt_usa" for table "sales_1_prt_2_2_prt_9"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_9_3_prt_europe" for table "sales_1_prt_2_2_prt_9"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_9_3_prt_asia" for table "sales_1_prt_2_2_prt_9"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_9_3_prt_other_regions" for table "sales_1_prt_2_2_prt_9"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_10_3_prt_usa" for table "sales_1_prt_2_2_prt_10"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_10_3_prt_europe" for table "sales_1_prt_2_2_prt_10"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_10_3_prt_asia" for table "sales_1_prt_2_2_prt_10"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_10_3_prt_other_regions" for table "sales_1_prt_2_2_prt_10"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_11_3_prt_usa" for table "sales_1_prt_2_2_prt_11"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_11_3_prt_europe" for table "sales_1_prt_2_2_prt_11"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_11_3_prt_asia" for table "sales_1_prt_2_2_prt_11"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_11_3_prt_other_regions" for table "sales_1_prt_2_2_prt_11"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_12_3_prt_usa" for table "sales_1_prt_2_2_prt_12"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_12_3_prt_europe" for table "sales_1_prt_2_2_prt_12"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_12_3_prt_asia" for table "sales_1_prt_2_2_prt_12"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_12_3_prt_other_regions" for table "sales_1_prt_2_2_prt_12"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_13_3_prt_usa" for table "sales_1_prt_2_2_prt_13"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_13_3_prt_europe" for table "sales_1_prt_2_2_prt_13"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_13_3_prt_asia" for table "sales_1_prt_2_2_prt_13"
NOTICE: CREATE TABLE will create partition "sales_1_prt_2_2_prt_13_3_prt_other_regions" for table "sales_1_prt_2_2_prt_13"
CREATE TABLE
sachi=# \d sales 
Table "public.sales"
Column | Type | Modifiers 
--------+---------+-----------
id | integer | 
year | integer | 
month | integer | 
day | integer | 
region | text | 
Number of child tables: 2 (Use \d+ to list them.)
Distributed by: (id)
sachi=# 



sachi=# \d+ sales
Table "public.sales"
Column | Type | Modifiers | Storage | Description 
--------+---------+-----------+----------+-------------
id | integer | | plain | 
year | integer | | plain | 
month | integer | | plain | 
day | integer | | plain | 
region | text | | extended | 
Child tables: sales_1_prt_2,
sales_1_prt_outlying_years
Has OIDs: no
Distributed by: (id)


sachi=# select * from pg_partitions where tablename='sales';


schemaname | tablename | partitionschemaname | partitiontablename | partitionname | parentpartitiont
ablename | parentpartitionname | partitiontype | partitionlevel | partitionrank | partitionposition | partitionlistvalues | partitionrangestart | 
partitionstartinclusive | partitionrangeend | partitionendinclusive | partitioneveryclause | partitionisdefault | partitionboundary | pare
nttablespace | partitiontablespace 
------------+-----------+---------------------+-----------------------------------------------------------------+----------------+---------------------------
--------------------+---------------------+---------------+----------------+---------------+-------------------+---------------------+---------------------+-
------------------------+-------------------+-----------------------+----------------------+--------------------+--------------------------------------+-----
-------------+---------------------
public | sales | public | sales_1_prt_outlying_years | outlying_years | 
| | range | 0 | | 1 | | | 
f | | f | | t | DEFAULT PARTITION outlying_years | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2 | | 
| | range | 0 | 1 | 2 | | 2012 | 
t | 2013 | f | 1 | f | START (2012) END (2013) EVERY (1) | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2_2_prt_other_months | other_months | sales_1_prt_2 
| | range | 1 | | 1 | | | 
f | | f | | t | DEFAULT SUBPARTITION other_months | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2_2_prt_2 | | sales_1_prt_2 
| | range | 1 | 1 | 2 | | 1 | 
t | 2 | f | 1 | f | START (1) END (2) EVERY (1) | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2_2_prt_3 | | sales_1_prt_2 
| | range | 1 | 2 | 3 | | 2 | 
t | 3 | f | 1 | f | START (2) END (3) EVERY (1) | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2_2_prt_4 | | sales_1_prt_2 
| | range | 1 | 3 | 4 | | 3 | 
t | 4 | f | 1 | f | START (3) END (4) EVERY (1) | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2_2_prt_5 | | sales_1_prt_2 
| | range | 1 | 4 | 5 | | 4 | 
t | 5 | f | 1 | f | START (4) END (5) EVERY (1) | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2_2_prt_6 | | sales_1_prt_2 
| | range | 1 | 5 | 6 | | 5 | 
t | 6 | f | 1 | f | START (5) END (6) EVERY (1) | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2_2_prt_7 | | sales_1_prt_2 
| | range | 1 | 6 | 7 | | 6 | 
t | 7 | f | 1 | f | START (6) END (7) EVERY (1) | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2_2_prt_8 | | sales_1_prt_2 
| | range | 1 | 7 | 8 | | 7 | 
t | 8 | f | 1 | f | START (7) END (8) EVERY (1) | pg_d
efault | pg_default
public | sales | public | sales_1_prt_2_2_prt_9 | | sales_1_prt_2 
| | range | 1 | 8 | 9 | | 8 | 
t | 9 | f | 1 | f | START (8) END (9) EVERY (1) | pg_d
efault | pg_default
....
132 rows


sachi=# select * from pg_catalog.pg_partition_columns where tablename='sales';

 schemaname | tablename | columnname | partitionlevel | position_in_partition_key 
------------+-----------+------------+----------------+---------------------------
 public     | sales     | year       |              0 |                         1
 public     | sales     | month      |              1 |                         1
 public     | sales     | month      |              1 |                         1
 public     | sales     | region     |              2 |                         1
 public     | sales     | region     |              2 |                         1
(5 rows)

Comments