From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: pgsql-sql@postgresql.org
Subject: check_constraint and Extract not working?
Date: Fri, 11 May 2007 18:14:55 -0300
Message-ID: <014b01c79411$6d216b10$8f01010a@iptel.com.ar> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fhevia@ip-tel.com.ar
Subject: Re: check_constraint and Extract not working?
In-Reply-To: <014b01c79411$6d216b10$8f01010a@iptel.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox