pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhere col1 not ilike ('str1%', 'str2%'... 'strN%') support?
10+ messages / 6 participants
[nested] [flat]
* where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
@ 2011-08-30 14:51 Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:01 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Julien Cigar <jcigar@ulb.ac.be>
2011-08-30 15:24 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Emi Lu @ 2011-08-30 14:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
@ 2011-08-30 15:01 ` Julien Cigar <jcigar@ulb.ac.be>
1 sibling, 0 replies; 10+ messages in thread
From: Julien Cigar @ 2011-08-30 15:01 UTC (permalink / raw)
To: pgsql-sql
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.
Attachments:
[text/x-vcard] jcigar.vcf (290B, ../../4E5CFB64.5060409@ulb.ac.be/2-jcigar.vcf)
download | inline:
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
@ 2011-08-30 15:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:48 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2011-08-30 15:24 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
Emi Lu <emilu@encs.concordia.ca> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:24 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-08-30 15:48 ` Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:53 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Emi Lu @ 2011-08-30 15:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 08/30/2011 11:24 AM, Tom Lane wrote:
> Emi Lu<emilu@encs.concordia.ca> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:24 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:48 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
@ 2011-08-30 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:57 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2011-08-30 15:53 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
Emi Lu <emilu@encs.concordia.ca> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:24 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:48 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:53 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-08-30 15:57 ` Emi Lu <emilu@encs.concordia.ca>
2011-08-31 07:16 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emre Hasegeli <hasegeli@tart.com.tr>
2011-08-31 10:17 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Jasen Betts <jasen@xnet.co.nz>
0 siblings, 2 replies; 10+ messages in thread
From: Emi Lu @ 2011-08-30 15:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:24 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:48 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:53 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:57 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
@ 2011-08-31 07:16 ` Emre Hasegeli <hasegeli@tart.com.tr>
2011-08-31 12:54 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
1 sibling, 1 reply; 10+ messages in thread
From: Emre Hasegeli @ 2011-08-31 07:16 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
2011/8/30 Emi Lu <emilu@encs.concordia.ca>:
> 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?
It is not. It is like "where id = (3, 5, 7)".
--
Emre Hasegeli
Veri Tabanı Yöneticisi
Tart İnternet Teknolojileri AŞ
tart.com.tr
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:24 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:48 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:53 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:57 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-31 07:16 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emre Hasegeli <hasegeli@tart.com.tr>
@ 2011-08-31 12:54 ` Emi Lu <emilu@encs.concordia.ca>
2011-08-31 16:16 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Emi Lu @ 2011-08-31 12:54 UTC (permalink / raw)
To: Emre Hasegeli <hasegeli@tart.com.tr>; +Cc: pgsql-sql
On 08/31/2011 03:16 AM, Emre Hasegeli wrote:
> 2011/8/30 Emi Lu<emilu@encs.concordia.ca>:
>
>> 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?
>
> It is not. It is like "where id = (3, 5, 7)".
What I mean is ilike ('%str1%', ... '%strN%')
I just forgot to put %
Emi
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:24 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:48 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:53 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:57 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-31 07:16 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emre Hasegeli <hasegeli@tart.com.tr>
2011-08-31 12:54 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
@ 2011-08-31 16:16 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Pavel Stehule @ 2011-08-31 16:16 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: Emre Hasegeli <hasegeli@tart.com.tr>; pgsql-sql
2011/8/31 Emi Lu <emilu@encs.concordia.ca>:
> On 08/31/2011 03:16 AM, Emre Hasegeli wrote:
>>
>> 2011/8/30 Emi Lu<emilu@encs.concordia.ca>:
>>
>>> 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?
>>
>> It is not. It is like "where id = (3, 5, 7)".
>
>
> What I mean is ilike ('%str1%', ... '%strN%')
>
> I just forgot to put %
it useless to introduce non SQL feature where some native feature exists now.
Regards
Pavel Stehule
>
> Emi
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:24 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:48 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:53 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:57 ` Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
@ 2011-08-31 10:17 ` Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 10+ messages in thread
From: Jasen Betts @ 2011-08-31 10:17 UTC (permalink / raw)
To: pgsql-sql
On 2011-08-30, Emi Lu <emilu@encs.concordia.ca> wrote:
> 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?
no, "('str1', ... 'strN')" looks a lot like a tuple.
if you know these strings beforehand use a single regular expression
instead because ilike is just regex in drag (postgres uses regex to do
ilike), and while ilike can only check one pattern at a time regex can
check several simultaneously.
foo ~* '^(str1|str2|str3...|strN)$'
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2011-08-31 16:16 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-30 14:51 where col1 not ilike ('str1%', 'str2%'... 'strN%') support? Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:01 ` Julien Cigar <jcigar@ulb.ac.be>
2011-08-30 15:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:48 ` Emi Lu <emilu@encs.concordia.ca>
2011-08-30 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-08-30 15:57 ` Emi Lu <emilu@encs.concordia.ca>
2011-08-31 07:16 ` Emre Hasegeli <hasegeli@tart.com.tr>
2011-08-31 12:54 ` Emi Lu <emilu@encs.concordia.ca>
2011-08-31 16:16 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-08-31 10:17 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox