Re: Postgres case insensitive searches

bhanu udaya wrote:
> What is the best way of doing case insensitive searches in postgres using Like.

Table "laurenz.t"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
val | text | not null
Indexes:
"t_pkey" PRIMARY KEY, btree (id)

CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops);

ANALYZE t;

EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%';

QUERY PLAN
------------------------------------------------------------------------------
Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4)
Index Cond: ((upper(val) ~>=~ 'AB'::text) AND (upper(val) ~<~ 'AC'::text))
Filter: (upper(val) ~~ 'AB%'::text)
(3 rows)

Yours,
Laurenz Albe