Re: Rule appears not to fire on insert w/ "except"

Dear Chris,

What about this:

insert into test1
select id, data from test2
where id, data not in (select id, data from test1);

of which one would expect the same results...

Yours,

Aarjan

Ps. notice that you are inserting data into a serial column (in your
examples as well), as far as I know this is not common practice since your
sequence is now not correct anymore.
Therefore, better would be:

insert into test1 (data)
select data from test2
where id, data not in (select id, data from test1);

or to make the id column in table test1 of the integer type.

----- Original Message -----
From: "Chris Kratz" <chris(dot)kratz(at)vistashare(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "Jerry Sievers" <jerry(at)jerrysievers(dot)com>
Sent: Tuesday, November 22, 2005 2:12 PM
Subject: Re: [GENERAL] Rule appears not to fire on insert w/ "except"

> On Monday 21 November 2005 08:05 pm, Jerry Sievers wrote:
> > Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> writes:
> > > Hello All,
> > >
> > > We have finally tracked down a bug in our application to a rewrite
rule
> > > on a table. In essence, the rewrite rule in question logs any inserts
to
> > > another table. This works correctly in all cases except where an
> > > "except" clause is used in the insert statement. In this case, the
rows
> > > are inserted into the primary table as expected, but the rule either
does
> > > not fire, or fires in such a way that nothing is placed in the changes
> > > table.
> >
> > You must be referring to something like;
> >
> > insert into foo
> > select *
> > from sometable
> > except
> > select *
> > from someothertable
> > ;
> >
> > If there's an EXCEPT clause on INSERT, I've never seen it.
> >
> > Perhaps you should post your insert query and your rule declaration.
> >
> > > As a side note, is there a way to see the final sql after all
"rewrite"
> > > rules have been processed? It might help us understand what is going
on.
> >
> > Not SQL but see config setting;
> >
> > debug_print_rewritten
>
> Hello Jerry,
>
> The insert statement is included in the test case. Here it is again.
>
> insert into test1
> select id,data from test2
> except select id,data from test1;
>
> The goal of the except was to only insert items from test2 that don't
already
> exist in test1.
>
> Thanks for the hint on debug_print_rewritten. I'll look into that.
>
> -Chris
>
> --
> Chris Kratz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>