pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedexcept command
7+ messages / 4 participants
[nested] [flat]
* except command
@ 2007-08-13 17:50 olivier boissard <olivier.boissard@cerene.fr>
2007-08-13 18:07 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
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 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 ` Re: except command Steve Holdoway <steve.holdoway@firetrust.com>
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 17:50 except command olivier boissard <olivier.boissard@cerene.fr>
2007-08-13 18:07 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2007-08-13 18:13 ` Steve Holdoway <steve.holdoway@firetrust.com>
2007-08-13 18:26 ` Re: except command 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 17:50 except command olivier boissard <olivier.boissard@cerene.fr>
2007-08-13 18:07 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 18:13 ` Re: except command Steve Holdoway <steve.holdoway@firetrust.com>
@ 2007-08-13 18:26 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 21:30 ` Re: except command olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
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 17:50 except command olivier boissard <olivier.boissard@cerene.fr>
2007-08-13 18:07 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 18:13 ` Re: except command Steve Holdoway <steve.holdoway@firetrust.com>
2007-08-13 18:26 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2007-08-13 21:30 ` olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-08-13 21:54 ` Re: except command 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 17:50 except command olivier boissard <olivier.boissard@cerene.fr>
2007-08-13 18:07 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 18:13 ` Re: except command Steve Holdoway <steve.holdoway@firetrust.com>
2007-08-13 18:26 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 21:30 ` Re: except command olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
@ 2007-08-13 21:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 22:13 ` Re: except command 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 17:50 except command olivier boissard <olivier.boissard@cerene.fr>
2007-08-13 18:07 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 18:13 ` Re: except command Steve Holdoway <steve.holdoway@firetrust.com>
2007-08-13 18:26 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-08-13 21:30 ` Re: except command olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-08-13 21:54 ` Re: except command Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2007-08-13 22:13 ` olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
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