Hi
I have a large table about 3B rows, that I would like to partition on a column called _received which is of type timestamp
I followed this
and came up with
CREATE OR REPLACE FUNCTION create_partition_and_insert() RETURNS trigger AS
$BODY$
DECLARE
partition_date TEXT;
partition TEXT;
BEGIN
partition_date := to_char(NEW._received,'YYYYMM');
partition := TG_TABLE_NAME || '_' || partition_date;
IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
RAISE NOTICE 'A partition has been created %',partition;
EXECUTE 'CREATE TABLE ' || partition || ' (check ( to_char(_received, '''YYYYMM''') = ''' || partition_date || ''')) INHERITS (' || TG_TABLE_NAME || ');';
END IF;
EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').* RETURNING patent_id;';
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Which I believe generates something like this
CREATE TABLE feedintra_201707( check (to_char(_received,'YYYYMM') = '201707' )) INHERITS (XXXXXX);
My concern is the cost of doing to_char on every row.
So
Is this going to work
Is this the right way to do it.
I was thinking if I could change the check to be something like
check ( _recieved >= YYYYMM 1 00:00 and _recieved <= YYYYMM <last day of the month> 23:59:59.999 )
so I am not sure how to make up the above line based on a timestamp
Thanks
Alex