Re: Check a column value not in Array.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi everybody,

I was reading the mails concerning the subject "Check a column value not in Array" and
made a quick test. This is the test table:

test=# select * from values;id |                                        item

- ----+----- 1 | a

 2 | b

 3 | ab

 4 | ac

 5 | c

 6 | abc


(6 Zeilen)

Now the first select suggested by Filip:

test=# select * from values where item <> ALL ( ARRAY['a', 'b'] );id |                                        item

- ----+------ 3 | ab

 4 | ac

 5 | c

 6 | abc


(4 Zeilen)

=> why is there 'c'?

No the second select suggested by Pavel:

test=# select * from values where item = any(array['a','b']);id |                                        item

- ----+------ 1 | a

 2 | b


(2 Zeilen)

=> this seems to be correct but I think it is not the result Emi Lu was expecting - or am
I wrong? Maybe you were looking for this:

test=# select * from values where item <= any(array['a','b']);id |                                        item

- ----+----- 1 | a

 2 | b

 3 | ab

 4 | ac

 6 | abc


(5 Zeilen)

Give me all rows, where a or b is in ...

Greetz

Andy

Filip Rembiałkowski schrieb:
> 2008/8/14 Emi Lu <emilu@encs.concordia.ca>:
>> Greetings,
>>
>> May I know the command to check whether a column value is in array please?
>>
>> For example, I am looking  for sth like:
>>
>> select *
>> from   test
>> where  test.col not in ARRAY['val1', 'val2'];
>>
> 
> select * from test where test.col <> ALL ( ARRAY['val1', 'val2'] );
> 
> see http://www.postgresql.org/docs/current/static/functions-comparisons.html
> 
> be careful with NULLs in this type of comparisons.
> 
> 
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIpTIhVa7znmSP9AwRAi1/AJ9r5v1TER1JD916+P2EkcyBu4mL+gCgkToT
Op4UjxVqH9N20uuzL25QMxU=
=bN5x
-----END PGP SIGNATURE-----