> On Friday 19 September 2003 09:00, [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html