Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "Girish Bajaj" <gbajaj@tietronix.com>, <pgsql-sql@postgresql.org>
Subject: Re: Table Partitioning and Rules
Date: Thu, 17 Jul 2003 19:52:07 +0100
User-Agent: KMail/1.4.3
References: <002f01c34c87$a72f59a0$7764a8c0@tietronix.com>
In-Reply-To: <002f01c34c87$a72f59a0$7764a8c0@tietronix.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307171952.07485.dev@archonet.com>

On Thursday 17 Jul 2003 6:20 pm, Girish Bajaj wrote:
> 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
> 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.

I'd not bother - databases are good at managing large amounts of informatio=
n.=20
If you really need to you can set up multiple partial indexes:

CREATE INDEX my_index_a ON contact (LastName) WHERE LastName>=3D'A' AND=20
LastName<'B'
CREATE INDEX my_index_b ON contact (LastName) WHERE LastName>=3D'B' AND=20
LastName<'C'
etc.

> 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?

Something like:
CREATE RULE ... ON INSERT TO Contact WHERE NEW.LastName>=3D'A' AND=20
NEW.LastName<'H'
DO INSTEAD ...insert into correct table here...

You'll want to read the chapter on the rule system in the "Server Programmi=
ng"=20
section of the manual.

--=20
  Richard Huxton