How to identify the ranges over which a postgres table was partitioned?

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.

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;

StackExchange Database Administrators Q#221277, answer score: 17