PostgreSQL Partition Check Script
New year is coming, if you want to check date based partitions in PostgreSQL , you may use the following query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
select schema_name, table_name, max( highvalue ) FROM ( SELECT namespace.nspname AS schema_name, parent.relname AS table_name, cons.consrc, to_date(substr((cast (regexp_matches( cons.consrc,'[[:digit:]]*-[[:digit:]]+-[[:digit:]]*','g') as varchar)),2,10),'YYYY-MM-DD') highvalue FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace namespace ON namespace.oid = parent.relnamespace JOIN pg_constraint cons ON cons.conrelid = child.oid WHERE cons.contype='c' ) ss group by schema_name, table_name order by 1,2,3 desc; |