Обсуждение: except command

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

except command

От
olivier boissard
Дата:
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"
Дата:
>>> On Mon, Aug 13, 2007 at 12:50 PM, in message <46C099F6.8050002@cerene.fr>,
olivier boissard <olivier.boissard@cerene.fr> 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
Дата:
On Mon, 13 Aug 2007 13:07:43 -0500
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

> >>> On Mon, Aug 13, 2007 at 12:50 PM, in message <46C099F6.8050002@cerene.fr>,
> olivier boissard <olivier.boissard@cerene.fr> 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"
Дата:
>>> On Mon, Aug 13, 2007 at  1:13 PM, in message
<20070814061358.d66d2cb3.steve.holdoway@firetrust.com>, Steve Holdoway
<steve.holdoway@firetrust.com> wrote:
> On Mon, 13 Aug 2007 13:07:43 -0500
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> 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"
Дата:
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
> <steve.holdoway@firetrust.com> wrote:
>
>> On Mon, 13 Aug 2007 13:07:43 -0500
>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> 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"
Дата:
>>> On Mon, Aug 13, 2007 at  4:30 PM, in message <46C0CD72.5090407@cerene.fr>,
"olivier.boissard@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"
Дата:
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" <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
>
>
>
>
>