Skip site navigation (1) Skip section navigation (2)

indices: ~* / text_ops

From: Bernhard Lorenz <bernhard(dot)lorenz(at)iconsult(dot)at>
To: pgsql-hackers(at)postgresql(dot)org
Subject: indices: ~* / text_ops
Date: 1998-10-02 16:01:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

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!


best regards,


Bernhard Lorenz                           Managing Partner
!C Internet Consult      
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 MomjianDate: 1998-10-02 16:05:14
Subject: Re: [HACKERS] SQL92
Previous:From: Jan WieckDate: 1998-10-02 15:45:06
Subject: Re: [HACKERS] Open 6.4 items

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group