Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 64D24475D82 for ; Mon, 1 Jul 2002 13:18:12 -0400 (EDT) Mailbox-Line: From tgl@sss.pgh.pa.us Mon Jul 1 13:18:12 2002 Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id E628D475D98 for ; Mon, 1 Jul 2002 13:17:31 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g61HHU215086; Mon, 1 Jul 2002 13:17:30 -0400 (EDT) To: Bruno Wolff III Cc: pgsql-sql@postgresql.org Subject: Re: Using union to avoid creating temp table In-reply-to: <20020701163140.GA22475@wolff.to> References: <20020701163140.GA22475@wolff.to> Comments: In-reply-to Bruno Wolff III message dated "Mon, 01 Jul 2002 11:31:40 -0500" Date: Mon, 01 Jul 2002 13:17:30 -0400 Message-ID: <15083.1025543850@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-5.3 required=5.0 tests=IN_REP_TO,X_NOT_PRESENT version=2.30 X-Spam-Level: X-Archive-Number: 200207/10 X-Sequence-Number: 8389 Bruno Wolff III writes: > What I ended up doing is building the input values into sepearte > select statements unioned together. So if the input values were > 'abc', 'def' and 'ghi', I would end up doing a select like: > select add.id from (select 'abc' union select 'def' union select 'ghi') add (id)where not exists (select from role where add.id = role.id) Might be worth writing "union all" not "union". This suppresses union's check for duplicated rows, which you presumably don't need. regards, tom lane