Message-ID: <42369A73.3050502@archonet.com>
Date: Tue, 15 Mar 2005 08:18:59 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: lucap@personainternet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: select multiple immediate values, but in multiple rows
References: <200503142318.27510.lucap@shaw.ca>
In-Reply-To: <200503142318.27510.lucap@shaw.ca>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Luca Pireddu wrote:
> Hello all.  I'd like to write a query does a set subtraction A - B, but A is 
> is a set of constants that I need to provide in the query as immediate 
> values.  I thought of something like
> 
> select a from (1,2,3.4)
> except
> select col_name from table;

richardh=> SELECT * FROM foo;
  a
---
  1
  2
  3
(3 rows)

richardh=> SELECT * FROM foo WHERE a NOT IN (1,2);
  a
---
  3
(1 row)

Run some tests with nulls in the column and the constant list too so you 
understand what happens in those cases.

-- 
   Richard Huxton
   Archonet Ltd