Re: Rule appears not to fire on insert w/ "except" - Mailing list pgsql-general

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.kratz@vistashare.com>
To: <pgsql-general@postgresql.org>
Cc: "Jerry Sievers" <jerry@jerrysievers.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.kratz@vistashare.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@postgresql.org so that your
>        message can get through to the mailing list cleanly
>
>