From: | Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
---|---|
To: | Remigiusz Sokolowski <rems(at)gdansk(dot)sprint(dot)pl>, Frank Morton <fmorton(at)base2inc(dot)com> |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] Newbie questions |
Date: | 1998-12-15 13:13:03 |
Message-ID: | l0311070eb29c0a1081e5@[147.233.159.109] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
At 10:26 +0200 on 14/12/98, Remigiusz Sokolowski wrote:
> > select * from animals where id='dogs' ignore case;
>
> look at operators in docs
> ~~ - LIKE operator
> ~* - match(regex), case insensitive operator
> I have no idea which is better
> Rem
> p.s. I could make some mistakes in this examples - check in amnual or docs
The like and regexp do a match rather than an equality test.
For an exact equality ignoring case, simply use
SELECT *
FROM animals
WHERE lower( id ) = 'dogs';
Note that this means an index on the id column will NOT be used, because
each value has to be converted to lower before testing. When I needed this
to be an indexed search, I added a column, called, say "lower_id", which
contains the lowercase version of the value in the "id" column. Then I
indexed that column and then you can ask WHERE lower_id = 'dogs' and get a
fast answer.
There is also the possibility of creating a functional index, but I haven't
managed to cause this sort of index to "kick in" on my version of postgres
(6.2.1).
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
From | Date | Subject | |
---|---|---|---|
Next Message | Herouth Maoz | 1998-12-15 13:30:12 | Re: [SQL] Data Dictionary |
Previous Message | Herouth Maoz | 1998-12-15 12:37:46 | Re: [SQL] binary search |