Re: Column defaults fail with rules on view

> On Friday 19 September 2003 09:00, btober(at)seaworthysys(dot)com wrote:
>> I'm finding that column defaults are not being assigned to nulls when
>> I do an insert by way of a an ON INSERT rule on a view. For example,
>> the following script
> [snip]
>
> Hmm - well, you're explicitly telling it to insert VALUES (...,
> new.field3, ...) so if new.field3 is null then it *should* do that.

I (apparently mistakenly) thought that the point of specifying DEFAULT
values in the table column definition was so that the default value would
be inserted automatically rather than a null. And anyway, that IS how it
seems to work when I do the insert for row A to the table directly with

INSERT INTO test_table VALUES ('A');

where fields 2, 3, and 4 have not been assigned values. Why do they get
the default in this case?

>
> Now - how you should go about getting the default I don't know. You
> could build a rule with WHERE NEW.field3 IS NULL and then not pass
> field3, but that would stop you explicitly setting it to null.

My work-around has been to define BEFORE INSERT triggers with lines like

SELECT INTO new.field3 COALESCE(new.field3, 1);

testing for and optionally assigning the default, but I really don't like
having to explicitly do that for every table and NOT NULL column, since I
make pretty much routine use of RULES on VIEWS to make writeable views
the interface to my user application.

>
> Out of curiosity, can you tell me what happens if you insert into the
> view ('C',DEFAULT,DEFAULT)?
> Richard Huxton

Same script, but with

INSERT INTO test_table VALUES ('A');
INSERT INTO test_table_v VALUES ('B');
INSERT INTO test_table_v VALUES ('C', DEFAULT, DEFAULT);

gives

field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
B | 2 | |
C | 3 | |
(3 rows)

-- so no change in behavior. I notice that field2, which was declared
type SERIAL, and so also has a DEFAULT, but one which calls the nextval
function rather than simply assigning a value, gets its default value
assigned in both the table insert and the view insert.

~Berend Tober