Dropping columns on partitioned tables.

Say you have a partitioned table and you want to add a column. There is no problem - you just add the column to the parent table, and it is added to all the children. But what if you want to drop a column? Then things are no so straightforward. If the child's column was created before it was inherited then it won't be dropped just by dropping it on the parent. So it very much depends on how the child is set up. If you do:

create table child() inherits (parent);

then dropping a column in the parent drops it in the child too. But if you do:

create table child (like parent);
alter table child inherit parent;

then dropping a column in the parent won't drop it in the child. The pg_partman package follows this second pattern when setting up child partitions, as I discovered yesterday when a client ran into this problem. In this case you have to delete the column from the children yourself. I devised the following snippet of code to accomplish this after you have deleted the column from the parent:

do $$
      declare 
        child oid;
      begin
        for child in
            select inhrelid
            from pg_inherits
            where inhparent = 'parent'::regclass
        loop
            execute 'alter table ' || child::regclass ||
                    ' drop column if exists some_column';
        end loop;
      end;
   $$;