> Hmm. I think this consideration boils down to whether the WHERE clause
> can give different results for rows that appear equal under the rules of
> UNION/EXCEPT/INTERSECT. If it gives the same result for any two such
> rows, then it's safe to push down; otherwise not.
>
> It's not too diffic
On Thu, 1 Aug 2002, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > So if T1 has a #dups>0 and T2 has a #dups>0 we should get
> > no rows, but what if T1' (with the clause) has a #dups>0 but
> > T2' has a #dups=0?
>
> Um, you're right --- pushing down into the right-hand side wou
Stephan Szabo <[EMAIL PROTECTED]> writes:
> So if T1 has a #dups>0 and T2 has a #dups>0 we should get
> no rows, but what if T1' (with the clause) has a #dups>0 but
> T2' has a #dups=0?
Um, you're right --- pushing down into the right-hand side would reduce
N, thereby possibly *increasing* the nu
On Thu, 1 Aug 2002, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > If we assume two collations one case sensitive one not with the
> > except in the non-sensitive and the where in the sensitive and
> > a left with 'A' and right with 'a', it'd be incorrect to push a
> > case sens
On Thu, 2002-08-01 at 18:02, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > For union, queries that want to do something like use a temporary
> > sequence to act sort of like rownum and do row limiting. Admittedly
> > that's already pretty much unspecified behavior, but it does c
Stephan Szabo <[EMAIL PROTECTED]> writes:
> Actually I think in except you may only push down to the left, since in
> this case you know that any duplicate from the right will not be
> returned (since there must be none). So, you can't potentially drop
> a row from the right side that may have be
On Thu, 1 Aug 2002, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > For union, queries that want to do something like use a temporary
> > sequence to act sort of like rownum and do row limiting. Admittedly
> > that's already pretty much unspecified behavior, but it does change
>
Stephan Szabo <[EMAIL PROTECTED]> writes:
> For union, queries that want to do something like use a temporary
> sequence to act sort of like rownum and do row limiting. Admittedly
> that's already pretty much unspecified behavior, but it does change
> the behavior in the place of duplicate remova
On Thu, 1 Aug 2002, Stephan Szabo wrote:
> On 1 Aug 2002, Hannu Krosing wrote:
>
> > On Thu, 2002-08-01 at 12:29, Curt Sampson wrote:
> > > On Thu, 1 Aug 2002, Zeugswetter Andreas SB SD wrote:
> > >
> > > > I had a "union all" view, which is actually a quite different animal than
> > > > a "unio
On 1 Aug 2002, Hannu Krosing wrote:
> On Thu, 2002-08-01 at 12:29, Curt Sampson wrote:
> > On Thu, 1 Aug 2002, Zeugswetter Andreas SB SD wrote:
> >
> > > I had a "union all" view, which is actually a quite different animal than
> > > a "union" view which needs to eliminate duplicates before furth
On Thu, 2002-08-01 at 12:29, Curt Sampson wrote:
> On Thu, 1 Aug 2002, Zeugswetter Andreas SB SD wrote:
>
> > I had a "union all" view, which is actually a quite different animal than
> > a "union" view which needs to eliminate duplicates before further processing.
>
> I had the same problem wit
On Thu, 1 Aug 2002, Zeugswetter Andreas SB SD wrote:
> I had a "union all" view, which is actually a quite different animal than
> a "union" view which needs to eliminate duplicates before further processing.
I had the same problem with UNION ALL.
cjs
--
Curt Sampson <[EMAIL PROTECTED]> +81
> But the message I was replying to was a similar union query, and I was
> thinking that that person might be having a similar initial intuitive
> reaction, "well, it looks kinda the same." I just wanted to note that
> you need to check this stuff with explain, rather than
> blindly assuming
> y
On Thu, 1 Aug 2002, Tom Lane wrote:
> But that isn't an "equivalent query". You've manually transformed
> SELECT * FROM (SELECT something UNION SELECT somethingelse) WHERE foo;
> into
> (SELECT something WHERE foo) UNION (SELECT somethingelse WHERE foo);
Right.
> As has been pointed ou
Curt Sampson <[EMAIL PROTECTED]> writes:
> On Thu, 1 Aug 2002, Tom Lane wrote:
>> Curt Sampson <[EMAIL PROTECTED]> writes:
> You want to be careful with this sort of stuff, since the query planner
> sometimes won't do the view as efficiently as it would do the fully
> specified equivalant query. I
On Thu, 1 Aug 2002, Tom Lane wrote:
> Curt Sampson <[EMAIL PROTECTED]> writes:
> > You want to be careful with this sort of stuff, since the query planner
> > sometimes won't do the view as efficiently as it would do the fully
> > specified equivalant query. I've posted about this here before.
>
Curt Sampson <[EMAIL PROTECTED]> writes:
> You want to be careful with this sort of stuff, since the query planner
> sometimes won't do the view as efficiently as it would do the fully
> specified equivalant query. I've posted about this here before.
Please provide an example. AFAIK a view is a
On Wed, 31 Jul 2002, Zeugswetter Andreas SB SD wrote:
> The utility is "Table Partitioning by expression".
>
> Basically you have a union view like:
> create view history as
> select * from history2000 where yearcol=2000
> union all
> select * from history2001 where yearcol=2001
You want to be c
> > Since I see a huge benefit in allowing conditional rules for a view,
> > I think it is worth finding a solution.
>
> We do allow conditional rules for a view. You just have to write an
> unconditional one too (which can be merely DO INSTEAD NOTHING).
Hmm, but you cannot then trow an error,
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes:
> Since I see a huge benefit in allowing conditional rules for a view,
> I think it is worth finding a solution.
We do allow conditional rules for a view. You just have to write an
unconditional one too (which can be merely DO INSTEAD NOTHI
> > Seems more accurate, but actually you may also have two or more
> > conditional rules that cover all possibilities if taken together.
> > Maybe
> > ERROR: Cannot insert into a view
> > You need an ON INSERT DO INSTEAD rule that matches your INSERT
> > Which covers both cases.
>
> Ac
Hannu Krosing <[EMAIL PROTECTED]> writes:
> On Wed, 2002-07-31 at 10:22, Tom Lane wrote:
>> Hm. How about
>>
>> ERROR: Cannot insert into a view
>> You need an unconditional ON INSERT DO INSTEAD rule
> Seems more accurate, but actually you may also have two or more
> conditional rules that cov
On Wed, 2002-07-31 at 10:22, Tom Lane wrote:
> Curt Sampson <[EMAIL PROTECTED]> writes:
> > On Wed, 31 Jul 2002, Tom Lane wrote:
> >> Well, to my mind that's what the error message says now. The reason
> >> it didn't help you was that you *did* have a rule ... but it didn't
> >> completely overri
On Wed, 31 Jul 2002, Tom Lane wrote:
> ERROR: Cannot insert into a view
> You need an unconditional ON INSERT DO INSTEAD rule
Sounds great to me!
cjs
--
Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light
Curt Sampson <[EMAIL PROTECTED]> writes:
> On Wed, 31 Jul 2002, Tom Lane wrote:
>> Well, to my mind that's what the error message says now. The reason
>> it didn't help you was that you *did* have a rule ... but it didn't
>> completely override the view insertion.
> Right, like I said, my model
On Wed, 31 Jul 2002, Tom Lane wrote:
> Well, to my mind that's what the error message says now. The reason
> it didn't help you was that you *did* have a rule ... but it didn't
> completely override the view insertion.
Right, like I said, my model was wrong. I didn't think of the error
message
Curt Sampson <[EMAIL PROTECTED]> writes:
> ERROR: Cannot insert into a view without an appropriate rule
>> Perhaps the error message could be phrased better --- any thoughts?
> Maybe a message that says something along the lines of "cannot insert
> into views; you need to override this behaviou
On Tue, 30 Jul 2002, Tom Lane wrote:
> Curt Sampson <[EMAIL PROTECTED]> writes:
> > CREATE VIEW test AS ...
> > CREATE RULE test_insert AS
> > ON INSERT TO test
> > DO ...
> > INSERT INTO test VALUES (1, 'one', 'onemore');
> > ERROR: Cannot insert into a view without an appropriate rule
Curt Sampson <[EMAIL PROTECTED]> writes:
> CREATE VIEW test AS ...
> CREATE RULE test_insert AS
> ON INSERT TO test
> DO ...
> INSERT INTO test VALUES (1, 'one', 'onemore');
> ERROR: Cannot insert into a view without an appropriate rule
> What am I doing wrong here? Is there a bug?
Mak
I'm having a weird problem on my " PostgreSQL 7.2.1 on i386--netbsdelf,
compiled by GCC 2.95.3" system. Executing these commands:
CREATE TABLE test_one (id int PRIMARY KEY, value_one text);
CREATE TABLE test_two (id int PRIMARY KEY, value_two text);
CREATE VIEW test AS
SELECT test_one.id, va
30 matches
Mail list logo
