pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
except command
7+ messages / 4 participants
[nested] [flat]

* except command
@ 2007-08-13 17:50  olivier boissard <olivier.boissard@cerene.fr>
  0 siblings, 1 reply; 7+ messages in thread

From: olivier boissard @ 2007-08-13 17:50 UTC (permalink / raw)
  To: pgsql-admin

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






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: except command
@ 2007-08-13 18:07  Kevin Grittner <Kevin.Grittner@wicourts.gov>
  parent: olivier boissard <olivier.boissard@cerene.fr>
  0 siblings, 1 reply; 7+ messages in thread

From: Kevin Grittner @ 2007-08-13 18:07 UTC (permalink / raw)
  To: olivier boissard <olivier.boissard@cerene.fr>; pgsql-admin

>>> 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
 





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: except command
@ 2007-08-13 18:13  Steve Holdoway <steve.holdoway@firetrust.com>
  parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
  0 siblings, 1 reply; 7+ messages in thread

From: Steve Holdoway @ 2007-08-13 18:13 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: except command
@ 2007-08-13 18:26  Kevin Grittner <Kevin.Grittner@wicourts.gov>
  parent: Steve Holdoway <steve.holdoway@firetrust.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Kevin Grittner @ 2007-08-13 18:26 UTC (permalink / raw)
  To: Steve Holdoway <steve.holdoway@firetrust.com>; pgsql-admin

>>> 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
 





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: except command
@ 2007-08-13 21:30  olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
  parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
  0 siblings, 1 reply; 7+ messages in thread

From: olivier.boissard@cerene.fr @ 2007-08-13 21:30 UTC (permalink / raw)
  To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin; +Cc: Steve Holdoway <steve.holdoway@firetrust.com>

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
>
>   




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: except command
@ 2007-08-13 21:54  Kevin Grittner <Kevin.Grittner@wicourts.gov>
  parent: olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
  0 siblings, 1 reply; 7+ messages in thread

From: Kevin Grittner @ 2007-08-13 21:54 UTC (permalink / raw)
  To: olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>; pgsql-admin; +Cc: Steve Holdoway <steve.holdoway@firetrust.com>

>>> 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
 





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: except command
@ 2007-08-13 22:13  olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
  parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
  0 siblings, 0 replies; 7+ messages in thread

From: olivier.boissard@cerene.fr @ 2007-08-13 22:13 UTC (permalink / raw)
  To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin

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
>  
>
>
>
>   




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2007-08-13 22:13 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-13 17:50 except command olivier boissard <olivier.boissard@cerene.fr>
2007-08-13 18:07 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 18:13   ` Steve Holdoway <steve.holdoway@firetrust.com>
2007-08-13 18:26     ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 21:30       ` olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-08-13 21:54         ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 22:13           ` olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox