Re: Using index for substring search

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Lec <englim(at)pc(dot)jaring(dot)my>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Using index for substring search
Date: 2002-03-13 00:10:42
Message-ID: 200203130010.g2D0AgY23142@candle.pha.pa.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

Lec wrote:
> Is it possible for postgreSQL 7.2 to use index search for the following
> queries:
>
> select name from item where substring(itemcode,1,3) = '101';
> select name from item where itemcode like '101%';
>
> if I have an index on itemcode.
>
> Version 6.5.3 can use index search for the first query.

Are you sure? 7.2 and I thought earlier releases could only use an
index on the second query. There is even an FAQ item.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

In response to

Browse pgsql-general by date

  From Date Subject
Next Message David Fetter 2002-03-13 01:21:26 Storing Credit Card Info?
Previous Message Trond Eivind =?iso-8859-1?q?Glomsr=F8d?= 2002-03-12 23:30:42 Re: Zlib vulnerability heads-up.