Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 770E1475A90 for ; Mon, 1 Jul 2002 12:26:25 -0400 (EDT) Mailbox-Line: From bruno@wolff.to Mon Jul 1 12:26:25 2002 Received: from wolff.to (wolff.to [66.92.219.49]) by postgresql.org (Postfix) with SMTP id B810F4759BC for ; Mon, 1 Jul 2002 12:26:19 -0400 (EDT) Received: (qmail 22538 invoked by uid 500); 1 Jul 2002 16:31:40 -0000 Date: Mon, 1 Jul 2002 11:31:40 -0500 From: Bruno Wolff III To: pgsql-sql@postgresql.org Subject: Using union to avoid creating temp table Message-ID: <20020701163140.GA22475@wolff.to> Mail-Followup-To: pgsql-sql@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.25i X-Spam-Status: No, hits=-1.9 required=5.0 tests=X_NOT_PRESENT version=2.30 X-Spam-Level: X-Archive-Number: 200207/8 X-Sequence-Number: 8387 I don't know if this is a well known trick or not. I wanted to check a list of values inputted to a script against some existing tables. I was trying to think of alternatives to using a temp table and copy since this would take two requests instead of one. 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) I don't know whether or not this is faster in general, but it probably is under at least some circumstances.