Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 830669FA15F for ; Fri, 11 May 2007 20:35:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 42615-02-3 for ; Fri, 11 May 2007 20:35:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from pyxis.powervt.com.ar (host3.powervt.com.ar [200.43.63.66]) by postgresql.org (Postfix) with ESMTP id 33B089FB30B for ; Fri, 11 May 2007 20:35:20 -0300 (ADT) Received: from pcfhevia ([201.216.249.98]) by pyxis.powervt.com.ar (IPGroup Mail Server) with ESMTP id 1OT48953 for ; Fri, 11 May 2007 18:13:05 -0300 From: "Fernando Hevia" To: Subject: check_constraint and Extract not working? Date: Fri, 11 May 2007 18:14:55 -0300 Organization: IP-TEL Message-ID: <014b01c79411$6d216b10$8f01010a@iptel.com.ar> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_014C_01C793F8.47D43310" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Thread-Index: AceUEWwRxbAckkjuTDe529wC1yGpiA== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/93 X-Sequence-Number: 28373 This is a multi-part message in MIME format. ------=_NextPart_000_014C_01C793F8.47D43310 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. ------=_NextPart_000_014C_01C793F8.47D43310 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable check_constraint and Extract not working?

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 =3D  1::DOUBLE = PRECISION);

ALTER TABLE = child_2 ADD CONSTRAINT = chk_child2_month CHECK (EXTRACT(MONTH FROM = row_date) = =3D  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.

------=_NextPart_000_014C_01C793F8.47D43310--