Re: Postgres case insensitive searches - Mailing list pgsql-general

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