From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Tomas Berndtsson <tomas(at)nocrew(dot)org> |
Cc: | "Hancock, David (DHANCOCK)" <DHANCOCK(at)arinc(dot)com>, "'pgsql-general(at)postgresql(dot)org '" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Simple Question: Case sensitivity |
Date: | 2000-12-11 15:21:54 |
Message-ID: | 7180.976548114@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tomas Berndtsson <tomas(at)nocrew(dot)org> writes:
> Related to this, is there any way to make an index for a table
> case-insensitive? If you have an index, but use upper() in the select,
> the index is not used.
Sure, make a functional index:
play=> create table foo (f1 text);
CREATE
play=> create index fooi on foo (upper(f1));
CREATE
This index will be considered for queries like:
play=> explain select * from foo where upper(f1) = 'z';
NOTICE: QUERY PLAN:
Index Scan using fooi on foo (cost=0.00..8.16 rows=10 width=12)
EXPLAIN
play=> explain select * from foo where upper(f1) > 'a' and upper(f1) < 'z';
NOTICE: QUERY PLAN:
Index Scan using fooi on foo (cost=0.00..8.21 rows=10 width=12)
EXPLAIN
You can use the same sort of ploy for lower() or any other simple
function of the table's columns. Don't go overboard with a ton of
indexes though; remember each index costs time when updating the
table...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Robert D. Nelson | 2000-12-11 15:23:00 | RE: Re: Re: Why PostgreSQL is not that popular as My |
Previous Message | Robert D. Nelson | 2000-12-11 15:18:00 | RE: storing passwords |