Thread: varchar_pattern_ops in 7.3.4?

I'd like to be able to do queries like "select count(*), fromline from
message where subject like 'Couldn%'" quickly.  I see that, in current
Postgres, I can do this with an index like "create index message_subject
on message using btree(subject varchar_pattern_ops)".  But I'm using
Postgres 7.3.4, and it doesn't seem to have varchar_pattern_ops in its
pg_opclass.opcname.  Is there an easier way to do these queries than
upgrading?

On Wed, 21 Jan 2004, Kragen Sitaker wrote:

> I'd like to be able to do queries like "select count(*), fromline from
> message where subject like 'Couldn%'" quickly.  I see that, in current
> Postgres, I can do this with an index like "create index message_subject
> on message using btree(subject varchar_pattern_ops)".  But I'm using
> Postgres 7.3.4, and it doesn't seem to have varchar_pattern_ops in its
> pg_opclass.opcname.  Is there an easier way to do these queries than
> upgrading?

The only way to make that version work well here is to dump your database,
reinitdb with --locate=C and reimport your data.  Then the indexes can be
used without having to have varchar_pattern_ops.

On Wed, Jan 21, 2004 at 04:54:26PM -0700, scott.marlowe wrote:
> On Wed, 21 Jan 2004, Kragen Sitaker wrote:
> > ... subject like 'Couldn%'" ... But I'm using Postgres 7.3.4, and
> > it doesn't seem to have varchar_pattern_ops in its pg_opclass.opcname.  ...
>
> The only way to make that version work well here is to dump your database,
> reinitdb with --locate=C and reimport your data.  Then the indexes can be
> used without having to have varchar_pattern_ops.

Thank you very much!  (I assume you mean --locale=C, not --locate.)