获取postgresql数据库中的分区数
答
这是你可以选择的表分区的所有名称:
SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child,
child.relname AS child_schema
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
它可以用来计数以及:
SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
COUNT(*)
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
GROUP BY
parent_schema,
parent;
答
这个问题是旧的。但a new question on dba.SE引用它,我觉得不得不添加一个更简单的解决方案。
据的问题,到...
获得在数据库中创建的分区数:
使用inheritance和特定父表,我想:
SELECT count(*) AS partitions
FROM pg_inherits i
WHERE i.inhparent = 'my_schema.my_parent_tbl'::regclass;
有关dba.SE的相关答案更多的细节:
Get all partition names for a table
如何删除最旧的分区? (首先创建的分区?) – Shay 2011-12-26 10:28:42
如果最低的OID是最旧的分区,则可以使用MIN(child.oid)来查找此分区。您可以使用它来查找此表的名称和模式,并使用它来删除此表。存储过程使其更容易维护。 – 2011-12-26 11:27:21