Re: [SQL] 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-----

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql