Post date: Mar 4, 2015 2:22:27 PM
Partitioning in PostgreSQL is based on inheritance and check constraints. It is easy to set up but doing it manually can introduce subtle with gaps in the partitions and so on.
Having an automatic generated partitioning will reduce such issues.
Do not run this on your production or test environment without inspecting it and approving it yourself first. It will DROP your customer table if you are not careful.
drp table if exists customer cascade;create table customer(a int primary key, b timestamp);DO LANGUAGE plpgsql $BODY$ DECLARE parent_table TEXT = 'customer'; partition_column TEXT = 'b'; primarykey_column TEXT = 'a'; start_date date = '2010-01-01'; end_date date = '2016-01-01'; partitioning_interval INTERVAL = interval '1 month'; partition_suffix_format TEXT = 'YYYYMM'; result TEXT;BEGIN SELECT string_agg(format(' CREATE TABLE %s (CONSTRAINT %1$s_%1$s_check CHECK((%2$s >= ''%3$s'' AND %2$s < ''%4$s'')) ) INHERITS (%5$s);', partition_name, partition_column, timestmp, timestmp + partitioning_interval, parent_table) || format(' ALTER TABLE ONLY %s ADD CONSTRAINT %s_PK PRIMARY KEY (%s);', partition_name, partition_name, primarykey_column ) ,' ' ) into result from generate_series(start_date::timestamp without time zone, end_date::timestamp without time zone, partitioning_interval ) ts(timestmp) cross join lateral (select format('%s_%s', parent_table, to_char(timestmp, partition_suffix_format) ) as partition_name) names; raise notice '%', result; execute result; END $BODY$