Hi,
I have problems with inserting rows into an updatable view through it's
insert rule.
Running this:
insert into view_products_1
(id,firmid,name_en,name_hu,artnum1,artnum2,description_hu,description_en,pkgunitid,minpkg,customstariff,vat)
values
('23','1','dddddddd','dddddddddddd','dddddddddd','dddddddd','ddddddddd','dddddddddd','1','10','2022220','20')
the engine sends this error:
ERROR: null value in column "qtyunitid" violates not-null constraint
********** Error **********
ERROR: null value in column "qtyunitid" violates not-null constraint
SQL state: 23502
But in the table definition I defined DEFULT=(-1) for this field. What's
going wrong? Shouldn't it inherit these settings from the table?
Many thanks,
--
Best Regards,
Csaba Együd
IN-FO Studio
Here is the table:
-------------------------------------------------------------------------------------------
CREATE TABLE whm.products
(
id serial NOT NULL,
firmid integer NOT NULL,
name_en character varying(250) NOT NULL DEFAULT ''::character varying,
name_hu character varying(250) NOT NULL DEFAULT ''::character varying,
artnum1 character varying(250) NOT NULL,
artnum2 character varying(250) NOT NULL DEFAULT ''::character varying,
description_hu character varying(512) NOT NULL DEFAULT ''::character
varying,
createtime timestamp with time zone NOT NULL DEFAULT now(),
"createuser" name NOT NULL DEFAULT "session_user"(),
lastmodtime timestamp with time zone NOT NULL DEFAULT now(),
lastmoduser name NOT NULL DEFAULT "session_user"(),
description_en character varying(512) NOT NULL DEFAULT ''::character
varying,
qtyunitid integer NOT NULL DEFAULT (-1),
pkgunitid integer NOT NULL DEFAULT (-1),
minpkg integer NOT NULL DEFAULT 0,
customstariff character varying(64) NOT NULL DEFAULT ''::character
varying,
vat numeric NOT NULL DEFAULT 20,
service boolean NOT NULL DEFAULT false,
notes character varying(512) DEFAULT ''::character varying,
CONSTRAINT pk_products_id PRIMARY KEY (id),
CONSTRAINT fk_products_firmid FOREIGN KEY (firmid) REFERENCES whm.firms
(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_products_qtyunitid FOREIGN KEY (qtyunitid) REFERENCES
whm.qtyunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT products_pkgunitid FOREIGN KEY (pkgunitid) REFERENCES
whm.pkgunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
And here is the definition of the view:
----------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW whm.view_products_1 AS
SELECT products.id, products.firmid, products.name_en, products.name_hu,
products.artnum1, products.artnum2, products.description_hu,
products.createtime, products.createuser, products.lastmodtime,
products.lastmoduser, products.description_en, products.qtyunitid,
products.pkgunitid, products.minpkg, products.customstariff, products.vat,
products.service, products.notes FROM whm.products WHERE products.firmid =
1;
CREATE OR REPLACE RULE view_products_1_insert AS
ON INSERT TO whm.view_products_1 DO INSTEAD INSERT INTO whm.products
(firmid, name_en, name_hu, artnum1, artnum2, description_hu, description_en,
qtyunitid, pkgunitid, minpkg, customstariff, vat, service, notes)
VALUES (1, new.name_en, new.name_hu, new.artnum1, new.artnum2,
new.description_hu, new.description_en, new.qtyunitid, new.pkgunitid,
new.minpkg, new.customstariff, new.vat, new.service, new.notes);
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
