I am wanting to partition my data based on a mod of one of the bigint columns, but when I run my insert test all the data goes into the base table and not the partitions. Here is what the table looks like:
CREATE table MyMappingTable ( id bigserial NOT NULL, c1 bigInt NOT NULL, c2 bigInt NOT NULL, c3 bigint NOT NULL, count bigint DEFAULT 1, createdTime timestamp with time zone default CURRENT_TIMESTAMP, CONSTRAINT MyMappingTable_index PRIMARY KEY (id) ) with (OIDS=FALSE); CREATE TABLE MyMappingTableT1 (PRIMARY KEY (id), CHECK((c1 % 5) = 0)) INHERITS (MyMappingTable); CREATE TABLE MyMappingTableT2 (PRIMARY KEY (id), CHECK((c1 % 5) = 1)) INHERITS (MyMappingTable); CREATE TABLE MyMappingTableT3 (PRIMARY KEY (id), CHECK((c1 % 5) = 2)) INHERITS (MyMappingTable); CREATE TABLE MyMappingTableT4 (PRIMARY KEY (id), CHECK((c1 % 5) = 3)) INHERITS (MyMappingTable); CREATE TABLE MyMappingTableT5 (PRIMARY KEY (id), CHECK((c1 % 5) = 4)) INHERITS (MyMappingTable); Here is the trigger function that I added to the database: CREATE OR REPLACE FUNCTION my_mapping_table_insert_trigger() RETURNS trigger AS $$ BEGIN IF ( (NEW.c1 % 5) = 0 ) THEN INSERT INTO MyMappingTableT1 VALUES (NEW.*); ELSIF ( (NEW.c1 % 5) = 1 ) THEN INSERT INTO MyMappingTableT2 VALUES (NEW.*); ELSIF ( (NEW.c1 % 5) = 2 ) THEN INSERT INTO MyMappingTableT3 VALUES (NEW.*); ELSIF ( (NEW.c1 % 5) = 3 ) THEN INSERT INTO MyMappingTableT4 VALUES (NEW.*); ELSIF ( (NEW.c1 % 5) = 4 ) THEN INSERT INTO MyMappingTableT5 VALUES (NEW.*); ELSE RAISE EXCEPTION 'c1 mod out of range. Something wrong with the my_mapping_table_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; Here is the Trigger that I added to the table: CREATE TRIGGER insert_my_mapping_table_trigger BEFORE INSERT ON MyMappingTable FOR EACH ROW EXECUTE PROCEDURE my_mapping_table_insert_trigger(); SET constraint_exclusion = ON; Regards, Richard
