2

If I create a table and partitions like this...

CREATE TABLE tab1 (a int, b int) PARTITION BY RANGE(a);
CREATE TABLE tab1_p1 PARTITION OF tab1 FOR VALUES FROM (0) TO (100);
CREATE TABLE tab1_p2 PARTITION OF tab1 FOR VALUES FROM (100) TO (200);

how can I subsequently check the ranges? I've tried browsing the information_schema.tables and information_schema.table_constraints but no luck so far.

1

You need to look into pg_class. You can use pg_get_expr() to get a readable expression for the partition bounds:

select pg_get_expr(c.relpartbound, c.oid, true)
from pg_class c
where relname = 'tab1_p1';

If you want to see that for all partitions (and sub-partitions) of one table, you can use the following:

select pt.relname as partition_name,
       pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression
from pg_class base_tb 
  join pg_inherits i on i.inhparent = base_tb.oid 
  join pg_class pt on pt.oid = i.inhrelid
where base_tb.oid = 'public.tab1'::regclass;
1

The partition specification is in the pg_class.relpartbound column; it's not in a human-readable format, so use pg_get_expr() to show it:

testdb=# select relname, pg_get_expr(relpartbound, oid) from pg_class where relispartition and relname~'tab1';
 relname |          pg_get_expr           
---------+--------------------------------
 tab1_p1 | FOR VALUES FROM (0) TO (100)
 tab1_p2 | FOR VALUES FROM (100) TO (200)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.