Just Code‎ > ‎

PostgreSQL - Automatic Partition creation script

posted Mar 4, 2015, 6:22 AM by Peter Henell   [ updated Mar 4, 2015, 6:47 AM ]
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$
Comments