Default values for nulls not being set.
От | Donald Fraser |
---|---|
Тема | Default values for nulls not being set. |
Дата | |
Msg-id | 003901c2e1d2$16a6d810$1664a8c0@DEMOLITION обсуждение исходный текст |
Ответы |
Re: Default values for nulls not being set.
|
Список | pgsql-bugs |
PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96 Given the following SQL: CREATE TABLE "tbl_test" ( "id" int4 NOT NULL, "b_disabled" bool DEFAULT '0' NOT NULL, "s_desc" text NOT NULL, "dt_edited" timestamp (0) without time zone DEFAULT 'now' NOT NULL, CONSTRAINT "tbl_test_pkey" PRIMARY KEY ("id"), CONSTRAINT "tbl_test_desc_key" UNIQUE ("s_desc") ) WITHOUT OIDS; GRANT ALL ON "tbl_test" TO PUBLIC; CREATE VIEW vu_tbl_test AS SELECT id, s_desc, b_disabled, dt_edited FROM tb= l_test; GRANT SELECT, INSERT, UPDATE ON TABLE vu_tbl_test TO GROUP PUBLIC; CREATE RULE rul_vu_test_i1 AS ON INSERT TO vu_tbl_test DO INSTEAD (INSERT I= NTO tbl_test (id, s_desc, b_disabled) VALUES(NEW.id, NEW.s_desc, NEW.b_disa= bled)); CREATE RULE rul_vu_tbl_test_u1 AS ON UPDATE TO vu_tbl_test DO INSTEAD (UPDA= TE tbl_test SET s_desc=3DNEW.s_desc, b_disabled=3DNEW.b_disabled WHERE (id = =3D NEW.id)); I have a major problem with DEAFAULT values: For example executing the following SQL: Bugs=3D> INSERT INTO tbl_test (id, s_desc) VALUES('1', 'Std insert test'); INSERT 0 1 This works ok but the equivalent for the view does not: Bugs=3D> INSERT INTO vu_tbl_test (id, s_desc) VALUES('2', 'View insert test= '); ERROR: ExecInsert: Fail to add null value in not null attribute b_disabled A further investigation: Bugs=3D> INSERT INTO tbl_test (id, s_desc, b_disabled) VALUES('3', 'Null in= sert test', NULL); ERROR: ExecInsert: Fail to add null value in not null attribute b_disabled shows that default values are not being set when NULL values are specified = in an INSERT statement. This might be ok for straight tables, as the client software can easily avo= id INSERTS with NULLs, but this situation is totally unusable for views bec= ause you don't know whether the user was supplying a NULL or whether it sim= ply wasn't specified. Even if did want to avoid this NULL situation the num= ber of permutations for the INSERT statements would escalate to the ridicul= ous when you have anything beyond 2 such columns. Any thoughts or work-arounds for views would be welcomed. Regards Donald Fraser.
В списке pgsql-bugs по дате отправления: