Just to be sure I am getting this right:
I have a big table I want to partition:
create table big_table (
row_date timestamp with time zone,
row_data character varying(80)
};
A nice solution would be to spread its rows in one of 12 child tables
according to which month the date field belongs to.
So my parent table is partitioned into 12 childs, one for each month:
child_1, child_2, ..., child_12.
My check constraints go like this:
ALTER TABLE child_1 ADD CONSTRAINT chk_child1_month CHECK (EXTRACT(MONTH
FROM row_date = 1::DOUBLE PRECISION);
ALTER TABLE child_2 ADD CONSTRAINT chk_child2_month CHECK (EXTRACT(MONTH
FROM row_date) = 2::DOUBLE PRECISION);
...
Well, the check_constraint exclusion won't work with these. I assume the
function Extract is the problem here and haven't been able to find a
workaround.
I have seen several examples where a table is partitioned by date but in
those cases the year is also specified. ie: child_200612, child_200701,
child_200702, etc. Though with this scenario I can avoid date functions in
the check constraint, this would force me to keep creating new child tables
from time to time.
I would really like to avoid that kind of maintenance, and for my case 12
partitions are quite enough.
Any suggestions how to achieve this otherwise?
Regards,
Fernando.
