except command

Поиск
Список
Период
Сортировка
Искать

except command

От:
olivier boissard <olivier.boissard@cerene.fr>
Дата:
Hello

I tried to use the except command in postgresql 8.1
I don't understand as it works
When I read doc , I understand that it was like a difference betwwen two 
queries
However , It looks as if the 2nd part was ignored
I tried to test by dblink a query unising except on the same database  
and all the records where returned
Do anybody know how it works ?

exemple:
select
    t1.columns   .....
from
    table_1  t1
EXCEPT
select
    t2.same_columns   .....
from
    table_2 t2

Olivier



Re: except command

От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
>>> On Mon, Aug 13, 2007 at 12:50 PM, in message <46C099F6.8050002@cerene.fr>,
olivier boissard  wrote: 
> I tried to use the except command in postgresql 8.1
> I don't understand as it works
> When I read doc , I understand that it was like a difference betwwen two 
> queries

That isn't how I read it:
 
"The EXCEPT operator returns the rows that are in the first result set
but not in the second."
 
( http://www.postgresql.org/docs/8.1/interactive/sql-select.html )
 
Are you seeing something different?
 
This gets me all the eye color codes that don't start with the letter 'B'.
(There are of course easier ways to get that...)
 
bigbird=> select * from "EyeColorCode" except select * from "EyeColorCode" where "eyeColorCode" like 'B%';
 eyeColorCode |      descr      | isActive | dotEyeColorCode
--------------+-----------------+----------+-----------------
 GRN          | Green           | t        | GRN
 GRY          | Gray            | t        | GRY
 HAZ          | Hazel           | t        | HAZ
 MAR          | Maroon          | t        | MAR
 MUL          | Multicolored    | t        | DIC
 PNK          | Pink            | t        | PNK
 XXX          | Unknown         | t        | UNK
(7 rows)
 
-Kevin
 


Re: except command

От:
Steve Holdoway <steve.holdoway@firetrust.com>
Дата:
On Mon, 13 Aug 2007 13:07:43 -0500
"Kevin Grittner"  wrote:

> >>> On Mon, Aug 13, 2007 at 12:50 PM, in message <46C099F6.8050002@cerene.fr>,
> olivier boissard  wrote: 
> > I tried to use the except command in postgresql 8.1
> > I don't understand as it works
> > When I read doc , I understand that it was like a difference betwwen two 
> > queries
> 
> That isn't how I read it:
>  
> "The EXCEPT operator returns the rows that are in the first result set
> but not in the second."
>  
> ( http://www.postgresql.org/docs/8.1/interactive/sql-select.html )
>  
> Are you seeing something different?
>  
> This gets me all the eye color codes that don't start with the letter 'B'.
> (There are of course easier ways to get that...)
>  
> bigbird=> select * from "EyeColorCode" except select * from "EyeColorCode" where "eyeColorCode" like 'B%';
>  eyeColorCode |      descr      | isActive | dotEyeColorCode
> --------------+-----------------+----------+-----------------
>  GRN          | Green           | t        | GRN
>  GRY          | Gray            | t        | GRY
>  HAZ          | Hazel           | t        | HAZ
>  MAR          | Maroon          | t        | MAR
>  MUL          | Multicolored    | t        | DIC
>  PNK          | Pink            | t        | PNK
>  XXX          | Unknown         | t        | UNK
> (7 rows)
>  
> -Kevin
so is this faster than usiung 'where not exists' ??

Steve

Re: except command

От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
>>> On Mon, Aug 13, 2007 at  1:13 PM, in message
<20070814061358.d66d2cb3.steve.holdoway@firetrust.com>, Steve Holdoway
 wrote: 
> On Mon, 13 Aug 2007 13:07:43 -0500
> "Kevin Grittner"  wrote:
>> This gets me all the eye color codes that don't start with the letter 'B'.
>> (There are of course easier ways to get that...)
> so is this faster than usiung 'where not exists' ??

I was trying to show a valid use of EXCEPT, not an alternative.
 
-Kevin
 


Re: except command

От:
"olivier.boissard@cerene.fr" <olivier.boissard@cerene.fr>
Дата:
Thanks for your explanation
And your small example was better as one page documentation

I was totally wrong.
So it's like a filter on the first query

I think it's useful when we have complex queries .

Olivier

Kevin Grittner a écrit :
>>>> On Mon, Aug 13, 2007 at  1:13 PM, in message
>>>>         
> <20070814061358.d66d2cb3.steve.holdoway@firetrust.com>, Steve Holdoway
>  wrote: 
>   
>> On Mon, 13 Aug 2007 13:07:43 -0500
>> "Kevin Grittner"  wrote:
>>     
>>> This gets me all the eye color codes that don't start with the letter 'B'.
>>> (There are of course easier ways to get that...)
>>>       
>> so is this faster than usiung 'where not exists' ??
>>     
>
> I was trying to show a valid use of EXCEPT, not an alternative.
>  
> -Kevin
>  
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>
>   

Re: except command

От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
>>> On Mon, Aug 13, 2007 at  4:30 PM, in message <46C0CD72.5090407@cerene.fr>,
"olivier.boissard@cerene.fr"  wrote: 
> So it's like a filter on the first query

Exactly; I think that sums it up better than anything I said.
 
By the way, it does strike me as an odd omission that there is no set
operator in the ANSI standard to get you directly to the set of disjoint
elements.  With two datasets, a and b, you could always get there with:
 
(a EXCEPT b) UNION ALL (b EXCEPT a)
 
or with:
 
(a UNION ALL b) EXCEPT (a INTERSECT b)
 
Of course, you could store the sets in temporary tables to get there without
generating from scratch each time, if that is expensive.
 
-Kevin
 


Re: except command

От:
"olivier.boissard@cerene.fr" <olivier.boissard@cerene.fr>
Дата:
Yes
 I noticed  It was not an ANSI sql operator
I think it's a good solution to spare temporay tables or result set

I was searching a way to ease some réplication scripts but I don't think 
it will help me.
It's better to use it to get a couple of records inside complex queries 
from many tables .

Thanks for help

Olivier

Kevin Grittner a écrit :
>>>> On Mon, Aug 13, 2007 at  4:30 PM, in message <46C0CD72.5090407@cerene.fr>,
>>>>         
> "olivier.boissard@cerene.fr"  wrote: 
>   
>> So it's like a filter on the first query
>>     
>
> Exactly; I think that sums it up better than anything I said.
>  
> By the way, it does strike me as an odd omission that there is no set
> operator in the ANSI standard to get you directly to the set of disjoint
> elements.  With two datasets, a and b, you could always get there with:
>  
> (a EXCEPT b) UNION ALL (b EXCEPT a)
>  
> or with:
>  
> (a UNION ALL b) EXCEPT (a INTERSECT b)
>  
> Of course, you could store the sets in temporary tables to get there without
> generating from scratch each time, if that is expensive.
>  
> -Kevin
>  
>
>
>
>   

FAQ