Thread: Using index for "like 'ABC%'" type query

For current version of postgresql, is it possible for a query like below
to utilize an index?

select item_name from item where item_code like 'ABC%';

Earlier versions of postgresql, eg, 6.5.3 can utilize the index.


Thanks,
Thomas.

There is a module called "tsearch" in the contrib directory which works
quite well.
On Thursday, April 17, 2003, at 09:56  PM, lec wrote:

> For current version of postgresql, is it possible for a query like
> below to utilize an index?
>
> select item_name from item where item_code like 'ABC%';
>
> Earlier versions of postgresql, eg, 6.5.3 can utilize the index.
>
>
> Thanks,
> Thomas.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

See the FAQ about index usage.

---------------------------------------------------------------------------

Jeffrey Melloy wrote:
> There is a module called "tsearch" in the contrib directory which works
> quite well.
> On Thursday, April 17, 2003, at 09:56  PM, lec wrote:
>
> > For current version of postgresql, is it possible for a query like
> > below to utilize an index?
> >
> > select item_name from item where item_code like 'ABC%';
> >
> > Earlier versions of postgresql, eg, 6.5.3 can utilize the index.
> >
> >
> > Thanks,
> > Thomas.
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Fri, Apr 18, 2003 at 10:56:52AM +0800, lec wrote:
> For current version of postgresql, is it possible for a query like below
> to utilize an index?
>
> select item_name from item where item_code like 'ABC%';

Yes.  But locale is enabled by default now, and if you use anything
other than C (and other-than-C is increasingly the default on the
supportedplatforms), it won't work.  Just make sure that your locale
is C when you do initdb.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Andrew Sullivan <andrew@libertyrms.info> writes:
>> [ can't LIKE use an index? ]

> Yes.  But locale is enabled by default now, and if you use anything
> other than C (and other-than-C is increasingly the default on the
> supported platforms), it won't work.  Just make sure that your locale
> is C when you do initdb.

I recall someone floating a proposal that initdb should by default
initialize the database in C locale, not whatever-it-finds-in-the-
environment.  To get a non-C locale you'd have to give an explicit
command-line switch --- essentially, reversing the sense of the present
"initdb --no-locale" option.

I'm beginning to think that would be a good idea, given the increasing
prevalence of en_US as a platform locale setting.  Comments?

            regards, tom lane

Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> writes:
> >> [ can't LIKE use an index? ]
>
> > Yes.  But locale is enabled by default now, and if you use anything
> > other than C (and other-than-C is increasingly the default on the
> > supported platforms), it won't work.  Just make sure that your locale
> > is C when you do initdb.
>
> I recall someone floating a proposal that initdb should by default
> initialize the database in C locale, not whatever-it-finds-in-the-
> environment.  To get a non-C locale you'd have to give an explicit
> command-line switch --- essentially, reversing the sense of the present
> "initdb --no-locale" option.
>
> I'm beginning to think that would be a good idea, given the increasing
> prevalence of en_US as a platform locale setting.  Comments?

Agreed, or we could special-case en_US to be C locale.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

I whole heartedly agree. It would make it easier to do multilangugages in one db, I think.

When I get done with my current project, about a year from now, I'm going to dig in deep to UTF-8 on postgress and see
ifI cn write a collation function in C to be used in ORDER BY, statements. 

Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> writes:
>
>>>[ can't LIKE use an index? ]
>
>
>>Yes.  But locale is enabled by default now, and if you use anything
>>other than C (and other-than-C is increasingly the default on the
>>supported platforms), it won't work.  Just make sure that your locale
>>is C when you do initdb.
>
>
> I recall someone floating a proposal that initdb should by default
> initialize the database in C locale, not whatever-it-finds-in-the-
> environment.  To get a non-C locale you'd have to give an explicit
> command-line switch --- essentially, reversing the sense of the present
> "initdb --no-locale" option.
>
> I'm beginning to think that would be a good idea, given the increasing
> prevalence of en_US as a platform locale setting.  Comments?
>
>             regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Yes. I'd prefer C locale by default.

Usage of other locales seems to incur many issues. Those who know about
locale and want it are usually prepared to turn it on and use it given some
documentation. Those who don't should get C locale.

Link.

At 10:48 AM 4/18/2003 -0400, Tom Lane wrote:
>I recall someone floating a proposal that initdb should by default
>initialize the database in C locale, not whatever-it-finds-in-the-
>environment.  To get a non-C locale you'd have to give an explicit
>command-line switch --- essentially, reversing the sense of the present
>"initdb --no-locale" option.
>
>I'm beginning to think that would be a good idea, given the increasing
>prevalence of en_US as a platform locale setting.  Comments?
>
>                         regards, tom lane
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Friday 18 April 2003 02:56, lec wrote:
> For current version of postgresql, is it possible for a query like below
> to utilize an index?
>
> select item_name from item where item_code like 'ABC%';
>
> Earlier versions of postgresql, eg, 6.5.3 can utilize the index.
>
>

    I think some other DB projects use something that is called
"BEGINS WITH" operator. Some time ago I tried to write such operator for
postgres. Unfortunatelly at that time I had no experience with postgres
internals, so the functions are written in PL/PSQL !!! They are working all
right, but the whole thing should be rewriten in C. If You are interested,
I'll send You the "code". For us using non "C" collations, I do not know for
other way. Of course if "C" collation satisfy Your needs, You do not need to
do such "hacks".

Regards !

On Fri, Apr 18, 2003 at 10:48:21AM -0400, Tom Lane wrote:
> prevalence of en_US as a platform locale setting.  Comments?

I tend to agree, especially since there's no real fix after you've
initdb'd.

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

In article <3954.1050677301@sss.pgh.pa.us>,
Tom Lane  <tgl@sss.pgh.pa.us> wrote:
>I'm beginning to think that would be a good idea, given the increasing
>prevalence of en_US as a platform locale setting.  Comments?

And who's brilliant idea was that anyway?  Grrr.  'file' should NOT sort
next to 'FILE'.

mrc [more than slightly annoyed and off topic]
--
     Mike Castle      dalgoda@ix.netcom.com      www.netcom.com/~dalgoda/
    We are all of us living in the shadow of Manhattan.  -- Watchmen
fatal ("You are in a maze of twisty compiler features, all different"); -- gcc

Mike Castle writes:

> And who's brilliant idea was that anyway?  Grrr.  'file' should NOT sort
> next to 'FILE'.

Any reputable dictionary in the world would disagree with you.

--
Peter Eisentraut   peter_e@gmx.net