Re: Column defaults fail with rules on view

Richard Huxton <dev(at)archonet(dot)com> writes:
> On Friday 19 September 2003 09:00, btober(at)seaworthysys(dot)com wrote:
>> Is this supposed to work that way? I would expect field3 and field4 to
>> have their respective column defaults assigned on the second INSERT (row
>> B), just like on the first INSERT (row A).

> Hmm - well, you're explicitly telling it to insert VALUES (..., new.field3,
> ...) so if new.field3 is null then it *should* do that.

Exactly. The defaults attached to the underlying table determine what
gets added to an INSERT into the underlying table. In this case, since
the rule's INSERT specifies all the fields, there is no scope for those
defaults to apply.

What Berend actually wants is to attach column defaults to the *view*,
so that they apply to an INSERT mentioning the view. You can do this in
recent PG releases (7.3 for sure, not sure about 7.2) using ALTER TABLE
... ADD DEFAULT.

regards, tom lane