Table Partitioning and Rules

From: "Girish Bajaj" <gbajaj(at)tietronix(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Table Partitioning and Rules
Date: 2003-07-17 17:20:00
Message-ID: 002f01c34c87$a72f59a0$7764a8c0@tietronix.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

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";

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Joe Conway 2003-07-17 17:42:34 Re: Recursive request ...
Previous Message Richard Poole 2003-07-17 16:09:16 Re: unique value - trigger?