From: | Csaba Együd <csegyud(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Updatable Views - DEFAULT doesn't inherit from table??? |
Date: | 2008-12-05 13:15:36 |
Message-ID: | ghb9hl$2ibq$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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);
From | Date | Subject | |
---|---|---|---|
Next Message | Grzegorz Jaśkiewicz | 2008-12-05 13:49:33 | Re: Updatable Views - DEFAULT doesn't inherit from table??? |
Previous Message | Grzegorz Jaśkiewicz | 2008-12-05 09:42:27 | Re: serial |