PostgreSQL Partitioned Table Example Usage

partition.sql

drop table if exists data cascade;
create table data (
  id uuid not null default uuid_generate_v4(),
  month integer not null,
  data json not null);

drop table if exists data_201501;
create table data_201501 (
  check ( month = 201501 )
) inherits (data);
create index data_201501_id on data_201501 (id);

drop table if exists data_201502;
create table data_201502 ( check ( month = 201502 )) inherits (data);
create index data_201502_id on data_201502 (id);

-- ...

create or replace function data_insert_trigger()
returns trigger as $$
begin
  if ( new.month = 201501 ) then
    insert into data_201501 values (new.*);
  elsif ( new.month = 201502 ) then
    insert into data_201502 values (new.*);

  -- ...

  else
    RAISE EXCEPTION 'Date out of range.  Fix the data_insert_trigger() function!';
  end if;
  return null;
end;
$$
language plpgsql;

-- -----------------------------------------------------------------------------

insert into data (month, data) values (201501, '[1, 2, "foo", null]'::json);

SET constraint_exclusion = on;
SELECT count(*) FROM data WHERE month=201501;
SELECT * FROM data WHERE month=201501;