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; |
The query may look too complex to get such a simple information, but unfortunately PostgreSQL’s partitioning system is not much different than “Oracle version 7 style” manual partitioning. As you may see, I use pg_inherits table to connect parent table and child tables (partitions), and query the pg_constraint table to get the check constraints. Then there comes the magical, regular expression command to filter the date values from the constraints to find the high values of the partitions.
Important: It only works if you defined the partition constraints in the following format:
|
1 |
real_date < to_date(' 2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') |
When you run the query, you get something like this:
I hope it helps.
mehmet sülün
Gokhan Atil
mehmet sülün
mehmet sülün
Gokhan Atil
mehmet sülün