f 怎麼列出Postgres 10 各Partition 的定義 ~ 迪貝之家

怎麼列出Postgres 10 各Partition 的定義

 

我們碰到的問題
一定有人也碰過
所以找谷大哥問了一下
誰叫他是全世界最大的資料庫
How to identify the ranges over which a postgres table was partitioned?
其實主要癥結點在於使用 pg_get_expr這個function
原來partition的定義寫到metadata裡頭只有Postgres自己看得懂
要撈出來的話,得透過這個function來解譯成當初給的語法
select pt.relname as partition_name,
pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression
from pg_class base_tb , pg_inherits i ,pg_class pt
where i.inhparent = base_tb.oid
and pt.oid = i.inhrelid
and base_tb.oid = 'mytest.settlement_detail'::regclass;
我跑的結果如下 :
mytestdb=# select pt.relname as partition_name,
mytestdb-# pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression
mytestdb-# from pg_class base_tb , pg_inherits i ,pg_class pt
mytestdb-# where i.inhparent = base_tb.oid
mytestdb-# and pt.oid = i.inhrelid
mytestdb-# and base_tb.oid = 'mytest.settlement_detail'::regclass;
partition_name | partition_expression
-----------------------------------+----------------------------------------------
settlement_detail_201809 | FOR VALUES FROM ('20180901') TO ('20181001')
settlement_detail_201810 | FOR VALUES FROM ('20181001') TO ('20181101')
settlement_detail_201811 | FOR VALUES FROM ('20181101') TO ('20181201')
settlement_detail_201812 | FOR VALUES FROM ('20181201') TO ('20190101')
settlement_detail_201901 | FOR VALUES FROM ('20190101') TO ('20190201')
settlement_detail_201902 | FOR VALUES FROM ('20190201') TO ('20190301')
settlement_detail_201903 | FOR VALUES FROM ('20190301') TO ('20190401')
settlement_detail_201904 | FOR VALUES FROM ('20190401') TO ('20190501')
settlement_detail_201905 | FOR VALUES FROM ('20190501') TO ('20190601')
settlement_detail_201906 | FOR VALUES FROM ('20190601') TO ('20190701')
settlement_detail_201907 | FOR VALUES FROM ('20190701') TO ('20190801')
settlement_detail_201908 | FOR VALUES FROM ('20190801') TO ('20190901')
settlement_detail_201909 | FOR VALUES FROM ('20190901') TO ('20191001')
settlement_detail_201910 | FOR VALUES FROM ('20191001') TO ('20191101')
settlement_detail_201911 | FOR VALUES FROM ('20191101') TO ('20191201')
settlement_detail_201912 | FOR VALUES FROM ('20191201') TO ('20200101')
settlement_detail_202001 | FOR VALUES FROM ('20200101') TO ('20200201')
settlement_detail_202002 | FOR VALUES FROM ('20200201') TO ('20200301')
settlement_detail_202003 | FOR VALUES FROM ('20200301') TO ('20200401')
settlement_detail_202004 | FOR VALUES FROM ('20200401') TO ('20200501')