X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 3B710CCA8B for ; Thu, 17 Jul 2003 17:20:33 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 98733-08 for ; Thu, 17 Jul 2003 17:21:00 +0000 (GMT) Received: from hermes.tietronix.com (hermes.tietronix.com [216.138.125.222]) by svr1.postgresql.org (Postfix) with ESMTP id 84142CCA83 for ; Thu, 17 Jul 2003 14:20:30 -0300 (ADT) Received: from jabba [66.139.15.154] by hermes.tietronix.com with ESMTP (SMTPD32-8.00) id A88F374008A; Thu, 17 Jul 2003 12:10:39 -0500 From: "Girish Bajaj" To: Subject: Table Partitioning and Rules Date: Thu, 17 Jul 2003 12:20:00 -0500 Message-ID: <002f01c34c87$a72f59a0$7764a8c0@tietronix.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0030_01C34C5D.BE5951A0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=3.8 tagged_above=0.0 required=5.0 tests=HTML_30_40, RCVD_IN_NJABL, SMTPD_IN_RCVD, UPPERCASE_25_50, X_NJABL_OPEN_PROXY X-Spam-Level: *** X-Archive-Number: 200307/231 X-Sequence-Number: 14345 This is a multi-part message in MIME format. ------=_NextPart_000_0030_01C34C5D.BE5951A0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Hello All, =20 I have a set of tables partitioned horizontally. DML below.=20 =20 Essentially Im trying to store a persons information in a table in the database. Since we could have millions of people, with duplicates! Ive decided we need to partition the table into segments where all people with the LastName starting from A to G will be in one table. H-N will be in another table and O-Z in the third. Ive created a VIEW that does a UNION on all the tables. Now, Im trying to create an INSERT and UPDATE RULE on the VIEW that would direct an insert or update into the appropriate partition table depending on the person LastName.=20 I cant seem to figure this out. Does anyone know how to do this? =20 Thanks, Girish -- Table: public."contact_A_G" CREATE TABLE public."contact_A_G" ( "CONTACTID" int8 NOT NULL, "LastName" varchar(50), "FirstName" varchar(50), CONSTRAINT "contact_A_G_pkey" PRIMARY KEY ("CONTACTID") ) WITH OIDS; -- Table: public."contact_H_N" CREATE TABLE public."contact_H_N" ( "CONTACTID" int8 NOT NULL, "LastName" varchar(50), "FirstName" varchar(50), CONSTRAINT "contact_H_N_pkey" PRIMARY KEY ("CONTACTID") ) WITH OIDS; -- Table: public."contact_O_Z" CREATE TABLE public."contact_O_Z" ( "CONTACTID" int8 NOT NULL, "LastName" varchar(50), "FirstName" varchar(50), CONSTRAINT "contact_O_Z_pkey" PRIMARY KEY ("CONTACTID") ) WITH OIDS; CREATE VIEW Contact AS SELECT * FROM "Contact_A_G" UNION SELECT * FROM "Contact_H_M" UNION SELECT * FROM "Contact_N_Z"; ------=_NextPart_000_0030_01C34C5D.BE5951A0 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hello All,

I have a set of tables partitioned horizontally. DML below. =

Essentially Im trying to store a persons information in a table in = the database. Since we could have millions of people, with duplicates! Ive deci= ded we need to partition the table into segments where all people with the Last= Name starting from A to G will be in one table. H-N will be in another table and= O-Z in the third. Ive created a VIEW that does a UNION on all the= tables.

Now, Im trying to create an INSERT and UPDATE RULE on the VIEW that would direct an insert or update into the appropriate partition table depending on the person LastName.

I cant seem to figure this out. Does anyone know how to do this?

Thanks,
Girish

-- Table: public."contact_A_G"
CREATE TABLE public."contact_A_G" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_A_G_pkey" PRIMARY KEY ("CONTACTID")
) WITH OIDS;

-- Table: public."contact_H_N"
CREATE TABLE public."contact_H_N" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_H_N_pkey" PRIMARY KEY ("CONTACTID")
) WITH OIDS;

-- Table: public."contact_O_Z"
CREATE TABLE public."contact_O_Z" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_O_Z_pkey" PRIMARY KEY ("CONTACTID")
) WITH OIDS;

CREATE VIEW Contact AS
SELECT * FROM "Contact_A_G"
UNION
SELECT * FROM "Contact_H_M"
UNION
SELECT * FROM "Contact_N_Z";

------=_NextPart_000_0030_01C34C5D.BE5951A0--