From: Richard Huxton <dev@archonet.com>
To: Girish Bajaj <gbajaj@tietronix.com>
To: pgsql-sql@postgresql.org
Subject: Re: Table Partitioning and Rules
Date: Thu, 17 Jul 2003 19:52:07 +0100
Message-ID: <200307171952.07485.dev@archonet.com> (raw)
In-Reply-To: <002f01c34c87$a72f59a0$7764a8c0@tietronix.com>
References: <002f01c34c87$a72f59a0$7764a8c0@tietronix.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 information.
If you really need to you can set up multiple partial indexes:
CREATE INDEX my_index_a ON contact (LastName) WHERE LastName>='A' AND
LastName<'B'
CREATE INDEX my_index_b ON contact (LastName) WHERE LastName>='B' AND
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>='A' AND
NEW.LastName<'H'
DO INSTEAD ...insert into correct table here...
You'll want to read the chapter on the rule system in the "Server Programming"
section of the manual.
--
Richard Huxton
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: dev@archonet.com, gbajaj@tietronix.com
Subject: Re: Table Partitioning and Rules
In-Reply-To: <200307171952.07485.dev@archonet.com>
* 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