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