Re: insert rule not firing on insert with exists subselect

Chris Kratz <chris.kratz@vistashare.com> writes:
> create rule mycopyrule as on insert to table2 do insert into table3 (col1)
> values (new.col1);

> insert into table2 (col1) select col1 from table1 where not exists (select 1
> from table2 where table2.col1 = table1.col1); -- doesn't work

The rule-generated query executes after the original INSERT, and can see
its results.  So by the time the rule fires, there *is* an entry in
table2 matching the table1 value, and of course the EXISTS condition
fails.

Rules are not triggers and make poor substitutes for them.  You're going
to need to use a trigger for this...

            regards, tom lane