[SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-30 Thread Emi Lu 

Good morning,

Does psql provide something like the following query command?

select * from tablename
where col1 not ilike ('str1%', 'str2%'... 'strN%')

Thanks a lot!
Emi

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

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-30 Thread Julien Cigar 

use a regex ?

On 08/30/2011 16:51, Emi Lu wrote:

Good morning,

Does psql provide something like the following query command?

select * from tablename
where col1 not ilike ('str1%', 'str2%'... 'strN%')

Thanks a lot!
Emi




--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
<>
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-30 Thread Tom Lane 
Emi Lu  writes:
> Does psql provide something like the following query command?

> select * from tablename
> where col1 not ilike ('str1%', 'str2%'... 'strN%')

If you remember the operator name equivalent to ILIKE (~~*)
you can do

select * from tablename
where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));

regards, tom lane

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

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-30 Thread Emi Lu 

On 08/30/2011 11:24 AM, Tom Lane wrote:

Emi Lu  writes:

Does psql provide something like the following query command?



select * from tablename
where col1 not ilike ('str1%', 'str2%'... 'strN%')


If you remember the operator name equivalent to ILIKE (~~*)
you can do

select * from tablename
where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));


Thank you Tom!

If next version could have "not ilike ('', '')" added into window 
functions, that's will be great!


Emi

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

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-30 Thread Tom Lane 
Emi Lu  writes:
> On 08/30/2011 11:24 AM, Tom Lane wrote:
>> select * from tablename
>> where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));

> If next version could have "not ilike ('', '')" added into window 
> functions, that's will be great!

Why?  And what's this got to do with window functions?

regards, tom lane

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

Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?

2011-08-30 Thread Emi Lu 

Hi Tom,


select * from tablename
where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));



If next version could have "not ilike ('', '')" added into window
functions, that's will be great!


Why?  And what's this got to do with window functions?


First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will 
work for me.


But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it?

I have a feeling that windows functions deal with all fancy functions. I 
would consider ilike ('str'...) as a non-standard SQL command?


Emi


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