Re: Default values for nulls not being set.

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.

"Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> I have a major problem with DEAFAULT values:

Consider attaching a default to the view column. As-is, its default
is NULL (and I can't see any good argument for the factory default
being anything else).

tt=# 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
tt=# alter table vu_tbl_test alter column b_disabled set default '0';
ALTER TABLE
tt=# INSERT INTO vu_tbl_test (id, s_desc) VALUES('2', 'View insert test');
INSERT 0 1
tt=#

regards, tom lane

> "Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> > I have a major problem with DEAFAULT values:
>
> Consider attaching a default to the view column. As-is, its default
> is NULL (and I can't see any good argument for the factory default
> being anything else).

What would be nice is when you don't specify a default on the view, the default
from the table is used.
That way, for most cases, you would only need to specify the default in one
place and not for every view...

regards Don.

> tt=# 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
> tt=# alter table vu_tbl_test alter column b_disabled set default '0';
> ALTER TABLE
> tt=# INSERT INTO vu_tbl_test (id, s_desc) VALUES('2', 'View insert test');
> INSERT 0 1
> tt=#
>
> regards, tom lane
>

"Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> What would be nice is when you don't specify a default on the view, the default
> from the table is used.

What table? You seem to have an extremely narrow-minded concept of what
a view is.

regards, tom lane

> "Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> > What would be nice is when you don't specify a default on the view, the
default
> > from the table is used.
>
> What table? You seem to have an extremely narrow-minded concept of what
> a view is.
>
> regards, tom lane

In the above I was referring to columns that have a direct relationship with an
underlying table. Obviously when a column does not have this relationship then
a default from a table cannot be used. If have I overlooked something
fundamental about views that makes this idea sound ridiculous then I apologise
for my ignorance.

Regards,
Donald Fraser