Were you able to get it to insert with the bigserial being used on the table? Every time I go to do an insert into one of the inherited tables I am now getting the following exception:
org.hibernate.HibernateException: The database returned no natively generated identity value Is auto-increment supported on table partitioning? Regards, Richard ________________________________ From: Raghavendra <raghavendra....@enterprisedb.com> To: Richard Onorato <richard_onor...@yahoo.com> Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> Sent: Tuesday, May 21, 2013 1:06 PM Subject: Re: [GENERAL] Table Partitioning On Tue, May 21, 2013 at 11:03 PM, Richard Onorato <richard_onor...@yahoo.com> wrote: 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 I tried your test case, its working fine from my end and populating data properly to partition childs. insert into mymappingtable values (1,7,20,30,1,now()); insert into mymappingtable values (2,6,20,30,1,now()); insert into mymappingtable values (3,8,20,30,1,now()); insert into mymappingtable values (4,9,20,30,1,now()); insert into mymappingtable values (5,10,20,30,1,now()); postgres=# \dt+ MyMappingTable* List of relations Schema | Name | Type | Owner | Size | Description --------+------------------+-------+----------+------------+------------- public | mymappingtable | table | postgres | 0 bytes | public | mymappingtablet1 | table | postgres | 8192 bytes | public | mymappingtablet2 | table | postgres | 8192 bytes | public | mymappingtablet3 | table | postgres | 8192 bytes | public | mymappingtablet4 | table | postgres | 8192 bytes | public | mymappingtablet5 | table | postgres | 8192 bytes | --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
