Re: Default values, inserts, and rules... - Mailing list pgsql-general

On Tue, 20 Aug 2002 22:37:46 -0700
Sean Chittenden <sean@chittenden.org> wrote:


> CREATE RULE t_ins AS ON INSERT TO t DO
>   INSERT INTO t_log (s,c1,c2) VALUES (CURRVAL('t_s_seq'),NEW.c1,NEW.c2);
> INSERT INTO t (c2) VALUES ('a');
> ERROR:  ExecAppend: Fail to add null value in not null attribute c1
>
> I think this was brought up a while ago...  Tom, in response to your
> question, moving this into the parser I think would solve the above
> behaviors that I'd classify as incorrect.  Am I wrong or missing some
> way of getting the desired behavior?
>


If NEW.c1 doesn't contain the value, to use COALESCE() can avoid
raising that error. And the default value: 42 is correctly inserted into t_log.


 CREATE RULE t_ins AS ON INSERT TO t DO
   INSERT INTO t_log (s,c1,c2) VALUES (CURRVAL('t_s_seq'),
                                                           COALESCE(NEW.c1, 42),
                                                           NEW.c2);


Regards,
Masaru Sugawara