Thread: Check a column value not in Array.
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']; Thanks!
Hello 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']; > > Thanks! postgres=# select 1 = any(array[1,2,3]);?column? ----------t (1 row) postgres=# select 4 = any(array[1,2,3]);?column? ----------f (1 row) Regards Pavel Stehule > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
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. -- Filip Rembiałkowski
-----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-----
>> 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. Thanks a lot for all your inputs! For efficiency, I guess : (1) "... where test.col <> all ARRAY['val1', 'val2']" and (2) "... where test.col not in ('var1', 'var2')" provide the same query efficiency right?