Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2F0D89F9C3F for ; Mon, 13 Aug 2007 15:14:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 41922-03 for ; Mon, 13 Aug 2007 15:14:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from server.firetrust.net (mailserver.firetrust.com [209.213.221.13]) by postgresql.org (Postfix) with ESMTP id 99AD79F9C03 for ; Mon, 13 Aug 2007 15:14:02 -0300 (ADT) Received: from steve-desktop (203-173-173-151.dsl.dyn.ihug.co.nz [203.173.173.151] (may be forged)) by server.firetrust.net (8.14.1/8.14.1) with SMTP id l7DIDxpv004058 for ; Tue, 14 Aug 2007 06:14:00 +1200 Date: Tue, 14 Aug 2007 06:13:58 +1200 From: Steve Holdoway To: pgsql-admin@postgresql.org Subject: Re: except command Message-Id: <20070814061358.d66d2cb3.steve.holdoway@firetrust.com> In-Reply-To: <46C0579F.EE98.0025.0@wicourts.gov> References: <46C099F6.8050002@cerene.fr> <46C0579F.EE98.0025.0@wicourts.gov> Organization: Firetrust Ltd X-Mailer: Sylpheed 2.3.1 (GTK+ 2.10.11; i486-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-MailWasher-Server-Scanned: Checked by MailWasher server v2.2.3 (www.Firetrust.com) X-MailWasher-Server-Status: Clean X-Virus-Scanned: ClamAV 0.91.1/3947/Tue Aug 14 03:25:28 2007 on server.firetrust.net X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/134 X-Sequence-Number: 26465 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