postgres partition by month exmaple

schema-partitioning-1.sql

-- partition by month
-- https://www.postgresql.org/docs/9.5/static/ddl-partitioning.html
begin;

drop trigger if exists insert_master_trigger on master;
drop function if exists master_insert();
drop table if exists master cascade;

create table master(
    id bigserial primary key,
    ts bigint not null,
    data text
);

create or replace function master_insert() 
returns trigger as $$
declare
    table_name varchar;
    date_timestamp timestamp;
    ts_begin bigint;
    ts_end bigint;
begin
    date_timestamp := to_timestamp(new.ts) at time zone 'UTC';
    table_name := format('master_%s_%s', extract(year from date_timestamp), extract(month from date_timestamp));
    perform 1 from pg_class where relname = table_name limit 1;
    if not found
    then
        ts_begin := extract(epoch from date_trunc('month', date_timestamp));
        ts_end := extract(epoch from (date_trunc('month', date_timestamp) + interval '1 month'));
        execute format('create table %s (like master including all)', table_name);
        execute format('alter table %s inherit master, add check (ts >= %s and ts < %s)', 
            table_name, ts_begin, ts_end);
        execute format('create index idx_%s_ts on %s (ts);', table_name, table_name);
    end if;
    execute 'insert into ' || table_name || ' values ( ($1).* )' using new;
    return null;
end;
$$ 
language plpgsql;

create trigger insert_master_trigger before insert on master for each row execute procedure master_insert();

commit;