Thread: problem with select where like ']'
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 ...
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
> 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)
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
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
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
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
> > 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
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 ==========================================================================
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/