SQL

postgres automatic partioning a table

alter table myTable rename to myTable_old;

create table myTable_master(
    forDate date not null,
    key2 int not null,
    value int not null
) partition by range (forDate);

postgres automatic partioning a table

create function createPartitionIfNotExists(forDate date) returns void
as $body$
declare monthStart date := date_trunc('month', forDate);
    declare monthEndExclusive date := monthStart + interval '1 month';
    -- We infer the name of the table from the date that it should contain
    -- E.g. a date in June 2005 should be int the table mytable_200506:
    declare tableName text := 'mytable_' || to_char(forDate, 'YYYYmm');
begin
    -- Check if the table we need for the supplied date exists.
    -- If it does not exist...:
    if to_regclass(tableName) is null then
        -- Generate a new table that acts as a partition for mytable:
        execute format('create table %I partition of myTable_master for values from (%L) to (%L)', tableName, monthStart, monthEndExclusive);
        -- Unfortunatelly Postgres forces us to define index for each table individually:
        execute format('create unique index on %I (forDate, key2)', tableName);
    end if;
end;
$body$ language plpgsql;

postgres automatic partioning a table

create or replace view myTable as select * from myTable_master;

postgres automatic partioning a table

create or replace rule autoCall_createPartitionIfNotExists as on insert
    to myTable
    do instead (
        select createPartitionIfNotExists(NEW.forDate);
        insert into myTable_master (forDate, key2, value) values (NEW.forDate, NEW.key2, NEW.value)
    );

postgres automatic partioning a table

-- Finally copy the data to our new partitioned table
insert into myTable (forDate, key2, value) select * from myTable_old;

-- And get rid of the old table
drop table myTable_old;