在Postgresql数据库中跨边界查找分区

本文介绍了在Postgresql数据库中跨边界查找分区的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何通过该分区的范围（边界）信息在数据库目录中找到该分区？ 我使用日期字段对表进行了分区，我需要通过该信息查找分区。 我已经看到目录 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.