Finding partition keys in Greenplum

Post date: Oct 12, 2014 9:20:28 PM

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)