Next month partitions

When you do partition your tables monthly, then comes the question of when to create next partitions. I tend to create them just the week before next month and I have some nice nagios scripts to alert me in case I’ve forgotten to do so. How to check that by hand in the end of a month?

Here’s a catalog query to help you there:

 select * 
   from
   (
   select 'previous parts' as schemaname,
          count(*)::text as tablename
     from pg_tables
    where schemaname not in ('pg_catalog','information_schema')
      and tablename like to_char(now(), '%YYYYMM')
   
   union
   
   select schemaname,
          substring(tablename,1,length(tablename)-6) || '201108' 
     from pg_tables
    where schemaname not in ('pg_catalog','information_schema')
      and tablename like to_char(now(), '%YYYYMM')
   
   except
   
   select schemaname, tablename 
     from pg_tables
    where schemaname not in ('pg_catalog','information_schema')
      and tablename like to_char(now() + interval '1 month', '%YYYYMM')
   )
   as t
 order by schemaname <> 'previous parts',
          schemaname;

Which gives us, in our case:

   schemaname   |       tablename        
----------------+------------------------
 previous parts | 1
 central        | stats_entrantes_201108
(2 rows)

As you see, our partitions are named _YYYYMM so that’s it’s easy to match them in our queries, but I guess about everyone does about the same here. Then the to_char expressions only allow to not enter manually '%201108' in the query text. And there’s a trick so that we display how many partitions we have this month, adding a line to the result…