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@xxxxxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Cc: "Jerry Sievers" <jerry@xxxxxxxxxxxxxxxx>
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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx so that your
> message can get through to the mailing list cleanly
>
>
