[BUGS] Default values for nulls not being set.

[Donald Fraser]

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 tbl_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 INTO tbl_test (id, s_desc, b_disabled) VALUES(NEW.id, NEW.s_desc, NEW.b_disabled)); CREATE RULE rul_vu_tbl_test_u1 AS ON UPDATE TO vu_tbl_test DO INSTEAD (UPDATE tbl_test SET s_desc=NEW.s_desc, b_disabled=NEW.b_disabled WHERE (id = NEW.id));   I have a major problem with DEAFAULT values:   For example executing the following SQL: Bugs=> 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=> 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=> INSERT INTO tbl_test (id, s_desc, b_disabled) VALUES('3', 'Null insert 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 avoid INSERTS with NULLs, but this situation is totally unusable for views because you don't know whether the user was supplying a NULL or whether it simply wasn't specified. Even if did want to avoid this NULL situation the number of permutations for the INSERT statements would escalate to the ridiculous when you have anything beyond 2 such columns.   Any thoughts or work-arounds for views would be welcomed.   Regards Donald Fraser.