12

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.

4 Answers 4

18

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;
2

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' order by relname;
 relname |          pg_get_expr           
---------+--------------------------------
 tab1_p1 | FOR VALUES FROM (0) TO (100)
 tab1_p2 | FOR VALUES FROM (100) TO (200)
2

In order to reflect my environment, complementing @AdamKG 's answer, I would just add the partition schema name to the query:

select
    relnamespace::regnamespace as partition_schema,
    relname as partition_name,
    pg_get_expr(c.relpartbound, c.oid, true) as partition_criteria
from pg_class c
where relname ~ 'part_of_my_partitions_name'
1

The answers above are ideally what one expects to see. But for some reason if you would like to see it for just one table or tables under a given schema without writing a query, here's an indirect way:

  1. Take a back up of the table or tables using pgadmin 4 as a plain text.
  2. Open the exported file in a code editor or text editor to read the create table statements that would show the partition ranges.

Here's the official documentation on backing up using pgadmin4 https://www.pgadmin.org/docs/pgadmin4/development/backup_dialog.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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