Re: Question about pg_class column relpartbound - Mailing list pgsql-novice

I ran into the need to try and interpret this column as well and it turns out there's an easier way to have postgres do it for you. You can use one of two functions to get a more readable version of the partition bounds of a natively partitioned table:

pg_catalog.pg_get_expr()
pg_catalog.pg_get_partition_constraintdef()

For the first one you feed it the relpartbound column along with the oid of the associated table. This will output essentially the partition definition that you see when you run \d+ on any partitioned table. For example:

select pg_catalog.pg_get_expr(relpartbound, oid) from pg_class where oid = 'db_development_p2017_11_30_1500'::regclass;
                               pg_get_expr                               
--------------------------------------------------------------------------
 FOR VALUES FROM ('2017-11-30 15:00:00-05') TO ('2017-11-30 15:02:00-05')

The other function gives a more traditional output of the partition constraint:

select pg_get_partition_constraintdef('public.db_development_p2017_11_30_1500'::regclass);
                                                                       pg_get_partition_constraintdef                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (("timestamp" IS NOT NULL) AND ("timestamp" >= '2017-11-30 15:00:00-05'::timestamp with time zone) AND ("timestamp" < '2017-11-30 15:02:00-05'::timestamp with time zone))

I found both of these functions by turning on the ECHO_HIDDEN variable in psql and then just running \d+ on the given table. This shows the internal queries that postgres is using to produce the output that you see

\set ECHO_HIDDEN on
\d+ db_development_p2017_11_30_1500

One of the queries shown from running the above is this:

********* QUERY **********
SELECT inhparent::pg_catalog.regclass,
  pg_catalog.pg_get_expr(c.relpartbound, inhrelid),
  pg_catalog.pg_get_partition_constraintdef(inhrelid)
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits i ON c.oid = inhrelid
WHERE c.oid = '48709' AND c.relispartition;
**************************

Hope that helps

Keith
https://www.keithf4.com/