Skip site navigation (1) Skip section navigation (2)

Re: LIKE indexing

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: LIKE indexing
Date: 2001-08-20 15:58:05
Message-ID: 9106.998323085@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> peter=# create table test1 (a char(5));
> CREATE
> peter=# insert into test1 values ('four');
> INSERT 16560 1
> peter=# select * from test1 where a like 'four'::bpchar;
>  a
> ---
> (0 rows)

I think this is an erroneous result, actually, seeing as how

regression=# select 'four '::bpchar = 'four'::bpchar;
 ?column?
----------
 t
(1 row)

How can A = B not imply A LIKE B?  (This may be related to Hiroshi's
concerns.)

I dug into the spec to see what it has to say, and came up with this
rather opaque prose:

                 4) If the i-th substring specifier of PCV is neither an
                   arbitrary character specifier nor an arbitrary string
                   specifier, then the i-th substring of MCV is equal to
                   that substring specifier according to the collating
                   sequence of the <like predicate>, without the appending
                   of <space> characters to MCV, and has the same length as
                   that substring specifier.

The bit about "without the appending of <space> characters" *might*
mean that LIKE is always supposed to treat trailing blanks as
significant, but I'm not sure.  The text does seem to say that it's okay
to add trailing blanks to the pattern to produce a match, when the
collating sequence is PAD SPACE type (bpchar in our terms).

In any case, Hiroshi is dead right that LIKE is supposed to perform
collating-sequence-dependent comparison, and this probably means that
this whole approach is a dead end :-(

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2001-08-20 16:03:20
Subject: Re: Suggestion for To Do List - Client timeout please.
Previous:From: Doug McNaughtDate: 2001-08-20 15:55:42
Subject: Re: Re: CREATEDB Where ??

pgsql-patches by date

Next:From: Bruce MomjianDate: 2001-08-20 16:29:26
Subject: Re: pgcrypto update
Previous:From: Peter EisentrautDate: 2001-08-20 15:45:02
Subject: Re: LIKE indexing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group