Thread: problem with select where like ']'

problem with select where like ']'

From
hubert depesz lubaczewski
Date:
hi,
i have a table (view actually) which contains field "article_name" which is
written like:
[AGD]|[Kuchenki Mikrofalowe]|[Samsung AKMS1]
i.e. some string within "[]" delimited by "|"
i want to select all record that have "[AGD]|[" in front of them
so i tried 
select * from my_view where article_name like '[AGD]|[';
this doesn't work. no errors, but no tuples returned.
i checked that the problem is character ']'.
'[' works o.k. '|' - i guess works too. 
but inserting "]" makes the whole expression doesn't work
i tried escaping of it like
\], \\]
or even
\\\]
but none of these worked.
right now i'm using comparison:
where substring (...) = '...' and it works even with those "]" signs.
but i'm just wondering why like ']' doesn't work.
any ideas?

depesz

p.s. sorry for my english.

--  hubert depesz lubaczewski <=--=> adres www usuniêty na pro¶bê Asi ¦l.
------------------------------------------------------------------------    najwspanialsz± rzecz± jak± da³o nam
nowoczesnespo³eczeñstwo,     jest niesamowita wrêcz ³atwo¶æ unikania kontaktów z nim ...
 


Re: problem with select where like ']'

From
Tom Lane
Date:
hubert depesz lubaczewski <depesz@depesz.pl> writes:
> but i'm just wondering why like ']' doesn't work.

What LOCALE setting are you running the postmaster in?

']' is not a special character as far as LIKE is concerned, but
I suspect you may be seeing another variant of the problems that
LIKE index optimization has with peculiar collation rules.
You can find plenty of discussion of this in the mailing list archives
:-(

If there is an index on the field you are doing LIKE on, try dropping
the index to see if that makes the problem go away.

Another possibility is that you have been careless about always starting
the postmaster with the same LOCALE setting, in which case the index
may actually be corrupt (out of order) due to different records having
been inserted with different ideas about what the sort ordering should be.
In that case, dropping and recreating the index should help.
        regards, tom lane


Re: problem with select where like ']'

From
KuroiNeko
Date:
> hubert depesz lubaczewski <depesz@depesz.pl> writes:
> > but i'm just wondering why like ']' doesn't work.
>
> What LOCALE setting are you running the postmaster in?
>
> ']' is not a special character as far as LIKE is concerned, but
> I suspect you may be seeing another variant of the problems that
> LIKE index optimization has with peculiar collation rules.
OTOH,  it  seems like  it  shouldn't  return  any  rows, as  the  original
statement has no metacharachters, % or _


--
Well I tried to be meek And I have tried to be mildBut I spat like a woman And I sulked like a childI have lived behind
thewalls That have made me aloneStriven for peace Which I never have known
 
Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)



Re: problem with select where like ']'

From
Joseph Shraibman
Date:
Tom Lane wrote:
> 
> hubert depesz lubaczewski <depesz@depesz.pl> writes:
> > but i'm just wondering why like ']' doesn't work.
> 
> What LOCALE setting are you running the postmaster in?
> 
> ']' is not a special character as far as LIKE is concerned, but
> I suspect you may be seeing another variant of the problems that
> LIKE index optimization has with peculiar collation rules.
> You can find plenty of discussion of this in the mailing list archives
> :-(
> 

WHAT mailing list archives?

They aren't linked to anywhere on www.postgresql.org that I can find.

-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com


Re: problem with select where like ']'

From
Tom Lane
Date:
Joseph Shraibman <jks@selectacast.net> writes:
> WHAT mailing list archives?
> They aren't linked to anywhere on www.postgresql.org that I can find.

Hmm.  My bookmark is

http://www.postgresql.org/lists/mailing-list.html

Dunno how to get there from the site toplevel...
        regards, tom lane


Re: problem with select where like ']'

From
Bruce Momjian
Date:
The fact is, I can't figure out how to get there without the URL.


> Joseph Shraibman <jks@selectacast.net> writes:
> > WHAT mailing list archives?
> > They aren't linked to anywhere on www.postgresql.org that I can find.
> 
> Hmm.  My bookmark is
> 
> http://www.postgresql.org/lists/mailing-list.html
> 
> Dunno how to get there from the site toplevel...
> 
>             regards, tom lane
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: problem with select where like ']'

From
Joseph Shraibman
Date:
The only way I can find is to do a search on something, and select to
search in mailing lists. Then after the search returns click on a link,
and trucate the url to http://www.postgresql.org/mhonarc/

Bruce Momjian wrote:
> 
> The fact is, I can't figure out how to get there without the URL.
> 
> > Joseph Shraibman <jks@selectacast.net> writes:
> > > WHAT mailing list archives?
> > > They aren't linked to anywhere on www.postgresql.org that I can find.
> >
> > Hmm.  My bookmark is
> >
> > http://www.postgresql.org/lists/mailing-list.html
> >
> > Dunno how to get there from the site toplevel...
> >
> >                       regards, tom lane
> >
> 
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com


Re: problem with select where like ']'

From
Bruce Momjian
Date:
> 
> http://www.postgresql.org/users-lounge/index.html  has most of them.
> Actually it's under General Info from the user's lounge.
> 
> It was brought to my attention today that the list of archives was
> incomplete so I'll be adding to it and adding another one (developer 
> and user).  Don't get too attached to the url below, it will be going
> away very soon.

Oh, I see it now.  Thanks.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: problem with select where like ']'

From
Vince Vielhaber
Date:
http://www.postgresql.org/users-lounge/index.html  has most of them.
Actually it's under General Info from the user's lounge.

It was brought to my attention today that the list of archives was
incomplete so I'll be adding to it and adding another one (developer 
and user).  Don't get too attached to the url below, it will be going
away very soon.

Vince.

On Tue, 17 Oct 2000, Bruce Momjian wrote:

> The fact is, I can't figure out how to get there without the URL.
> 
> 
> > Joseph Shraibman <jks@selectacast.net> writes:
> > > WHAT mailing list archives?
> > > They aren't linked to anywhere on www.postgresql.org that I can find.
> > 
> > Hmm.  My bookmark is
> > 
> > http://www.postgresql.org/lists/mailing-list.html
> > 
> > Dunno how to get there from the site toplevel...
> > 
> >             regards, tom lane
> > 
> 
> 
> 

-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net128K ISDN from $22.00/mo - 56K Dialup from
$16.00/moat Pop4 Networking       Online Campground Directory    http://www.camping-usa.com      Online Giftshop
Superstore   http://www.cloudninegifts.com
 
==========================================================================





Re: problem with select where like ']'

From
Peter Eisentraut
Date:
Joseph Shraibman writes:

> The only way I can find is to do a search on something, and select to
> search in mailing lists. Then after the search returns click on a link,
> and trucate the url to http://www.postgresql.org/mhonarc/

When in doubt, use geocrawler.com.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/