Обсуждение: How to do A union (B - ( A intersect B )) or sort of :-)
Hi. Sorry for the subject, but I knew no words to describe it better. I will try to explain First a more general question: is there any clever way to do two selects A and B and then return the result A union ( B \ (A intersect B)) ( \ is "set subtraction") Any ideas besides writing the explicit queries? I guess one has to lock the table to get the same result on both selects unless one can cache the result of A and B so you don't run it twice? Then the actual problem at hand, which with extra details might result in another solution then the one from above. A looks like select a.id,a.name,true from X B looks like select b.id,b.name,false from Y can one then use the differences in boolean values in some clever way, like OR operation?
A B <gentosaker@gmail.com> writes: > Sorry for the subject, but I knew no words to describe it better. > I will try to explain > First a more general question: is there any clever way to do two > selects A and B and then return the result > A union ( B \ (A intersect B)) ( \ is "set subtraction") Ummm ... isn't that just a UNION? Anyway, SQL's features for this sort of thing are described here: http://www.postgresql.org/docs/9.0/static/queries-union.html > Any ideas besides writing the explicit queries? I guess one has to > lock the table to get the same result on both selects unless one can > cache the result of A and B so you don't run it twice? Both arms of a UNION or similar query would be reading the table with the same snapshot, so there wouldn't be any need for a lock to get consistent results. You may also need to spend some time reading http://www.postgresql.org/docs/9.0/static/mvcc.html regards, tom lane
Am 04.11.2010 20:53, schrieb A B: > First a more general question: is there any clever way to do two > selects A and B and then return the result > A union ( B \ (A intersect B)) ( \ is "set subtraction") > > Any ideas besides writing the explicit queries? I guess one has to > lock the table to get the same result on both selects unless one can > cache the result of A and B so you don't run it twice? > > Then the actual problem at hand, which with extra details might result > in another solution then the one from above. > > A looks like select a.id,a.name,true from X > B looks like select b.id,b.name,false from Y ( A intersect B ) is allways the empty set because of the boolean column, isn't it? Even if there were tupels (a.id, a.name) = (b.id, b.name) still (a.id, a.name, TRUE) <> (b.id, b.name, FALSE) because of TRUE <> FALSE So ( A intersect B ) = () Therefore ( B \ () ) = B So you end up with A union B as Tom said. The clever way to do the stuff w/o to many A- and B-subselects may be to create two temporary views and use those. There might be a performance loss though.