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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
ConanTheGerbil
  • 1,303
  • 5
  • 31
  • 50

4 Answers4

19

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

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)
Community
  • 1
AdamKG
  • 964
  • 5
  • 9
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'
ValerioOliveira
  • 41
  • 2
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

Dinesh Marimuthu
  • 11
  • 1