ive two serious problems with postgresql (latest version):
1.) i seem to be unable (well, i _am_ ;-) to create an index
on a text field and then have that index being used if
i perform a search using ~*, ~~, and related operators.
i found out that these operators can only successfully
be implemented if i use box* field types etc. this is a real
pain, since i have that database with more than 70,000
entries and it always does a sequential scan.
a query might look like
"select * from table where field ~* 'string'" or
"select * from table where field ~~ '%string%' etc.
there are indices (hash, btree, (field text_ops)), but
they wont be used.
can anybody of you possibly help me any further on this issue?
2.) another thing i noticed is that while "~*" is supposed to
perform a case insensitive search, it does not. i havent
checked too much into the bug behind it (like "all ascii
values > 127 ..."), but the typical austrian and german
characters like "ae" with two dots (forgot the terminologically
exact name, forgive me) etc. will not be searched
properly, thus, if i have a field entry with
and perform a search with
... ~* 'aeyadayada',
it wont find anything, i have to use ~* 'AEyadayada'.
opinions on that one woul dbe appreciated as well!
Bernhard Lorenz Managing Partner
!C Internet Consult http://www.iconsult.at/
Pacassistrasse 32, A-1130 Wien +43/1/319 09 90
Aichholzgasse 6/5, A-1120 Wien +43/1/817 39 23
pgsql-hackers by date
|Next:||From: Bruce Momjian||Date: 1998-10-02 16:05:14|
|Subject: Re: [HACKERS] SQL92|
|Previous:||From: Jan Wieck||Date: 1998-10-02 15:45:06|
|Subject: Re: [HACKERS] Open 6.4 items|