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