Looking further I just found that, if we don't want query to scan through child table then we should use ONLY during CREATE VIEW.
So if I replaced my create view query with: CREATE view view_tp_sales as SELECT * FROM ONLY tp_sales; Then INSERT stmt working find. So when you create VIEW on top of inheritance (partition) table you need to create it using ONLY keyword, right ? anyone please correct me if I am wrong. Regards, Rushabh On Tue, Jul 2, 2013 at 10:29 AM, <rushabh.lat...@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 8275 > Logged by: Rushabh Lathia > Email address: rushabh.lat...@gmail.com > PostgreSQL version: 9.2.4 > Operating system: All > Description: > > View based on inheritance throws error on insert statement. > > > Testcase: > > > DROP TABLE tp_sales cascade; > > > CREATE TABLE tp_sales > ( > salesman_id INT4, > salesman_name VARCHAR, > sales_region VARCHAR, > sales_amount INT4 > ); > > > create table tp_sales_p_india ( check (sales_region = 'INDIA')) inherits > (tp_sales); > create table tp_sales_p_rest (check (sales_region != 'INDIA')) inherits > (tp_sales); > > > CREATE OR REPLACE FUNCTION tp_sales_insert_trigger() > RETURNS TRIGGER AS $$ > BEGIN > IF NEW.sales_region = 'INDIA' THEN > INSERT INTO tp_sales_p_india VALUES (NEW.*); > ELSE > INSERT INTO tp_sales_p_rest VALUES (NEW.*); > END IF; > RETURN NULL; > END; > $$ > LANGUAGE plpgsql; > > > > > CREATE TRIGGER insert_tp_sales_trigger > BEFORE INSERT ON tp_sales > FOR EACH ROW EXECUTE PROCEDURE tp_sales_insert_trigger(); > > > INSERT INTO tp_sales VALUES (100,'Foo','INDIA',15000); > INSERT INTO tp_sales VALUES (110,'Bar','UK',24000); > > > CREATE view view_tp_sales as SELECT * FROM tp_sales; > > > -- run insert on view > postgres=# INSERT INTO view_tp_sales VALUES (120,'XYZ','INDIA',11000); > ERROR: new row for relation "tp_sales_p_rest" violates check constraint > "tp_sales_p_rest_sales_region_check" > DETAIL: Failing row contains (120, XYZ, INDIA, 11000). > postgres=# INSERT INTO view_tp_sales VALUES (120,'ABC','HELLO',11000); > ERROR: new row for relation "tp_sales_p_india" violates check constraint > "tp_sales_p_india_sales_region_check" > DETAIL: Failing row contains (120, ABC, HELLO, 11000). > postgres=# select version(); > version > > > ----------------------------------------------------------------------------------------------------------------- > PostgreSQL 9.4devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC) > 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit > (1 row) > > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Rushabh Lathia