在Postgresql数据库中跨边界查找分区 [英] Find a partition in a postgresql database across its boundaries

问题描述

如何通过该分区的范围（边界）信息在数据库目录中找到该分区？
我使用日期字段对表进行了分区，我需要通过该信息查找分区。
我已经看到目录 child表中有一个 relpartbound字段，但这似乎并不是一种简单的搜索方法。

How can I find a partition in the database catalog through the range (bounds) information of that partition? I partitioned a table using a date field and I need to find a partition through that information. I've seen that I have a "relpartbound" field in the catalog "child" table, but it does not seem to be in an easy way to search.

推荐答案

SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child,
    pg_get_expr(child.relpartbound, child.relfilenode) AS child_bounds
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
WHERE parent.relname = 'parent_table_name';

PostgreSql函数 pg_get_expr将反编译 child.relpartbound表达式的内部形式并给我一些东西更多readble像：

PostgreSql function "pg_get_expr" will decompile internal form of "child.relpartbound" expression and will give me something more readble like:

FOR VALUES FROM ('2017-01-01') TO ('2017-01-02')

Ty @a_horse_with_no_name是个很好的建议。

Ty @a_horse_with_no_name for the great suggestion.

这篇关于在Postgresql数据库中跨边界查找分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！