Thread: String matching
Is there a way to use indexes for queries like: select field from table where field like 'abc%' (i.e. filter for string fields that begin with something) ?
normally you shouldn't have to do anything, it should just work :
> select field from table where field like 'abc%'
CREATE INDEX ... ON table( field );
that's all
If it does not use the index, I saw on the mailing list that the locale
could be an issue.
PFC wrote: > > normally you shouldn't have to do anything, it should just work : > >> select field from table where field like 'abc%' > If it does not use the index, I saw on the mailing list that the > locale could be an issue. Oh yes, I forgot about that :( I do have LC_COLLATE (on latin2)... It's a shame PostgreSQL doesn't allow collation rules on specific fields - this field I'm using here will always be 7bit ASCII :(
On Mon, 14 Feb 2005, Ivan Voras wrote: > PFC wrote: > > > > normally you shouldn't have to do anything, it should just work : > > > >> select field from table where field like 'abc%' > > > If it does not use the index, I saw on the mailing list that the > > locale could be an issue. > > Oh yes, I forgot about that :( I do have LC_COLLATE (on latin2)... > > It's a shame PostgreSQL doesn't allow collation rules on specific fields > - this field I'm using here will always be 7bit ASCII :( You can also create an index using a <typename>_pattern_ops operator class which should be usable even with other collations.
Stephan Szabo wrote: > You can also create an index using a <typename>_pattern_ops operator > class which should be usable even with other collations. Could you give me an example for this, or point me to the relevant documentation?
On Mon, 14 Feb 2005, Ivan Voras wrote: > Stephan Szabo wrote: > > > You can also create an index using a <typename>_pattern_ops operator > > class which should be usable even with other collations. > > Could you give me an example for this, or point me to the relevant > documentation? Basically, you could have something like: create table test_table(a text); create index test_index on test_table(a text_pattern_ops); ------------------------------------------------------------------ http://www.postgresql.org/docs/8.0/interactive/indexes-opclass.html
Stephan Szabo wrote: > On Mon, 14 Feb 2005, Ivan Voras wrote: >>Could you give me an example for this, or point me to the relevant >>documentation? > > http://www.postgresql.org/docs/8.0/interactive/indexes-opclass.html Thanks! I didn't know this and I certainly didn't think it would be that easy :)
On Mon, 14 Feb 2005, Ivan Voras wrote: > Stephan Szabo wrote: > > On Mon, 14 Feb 2005, Ivan Voras wrote: > > >>Could you give me an example for this, or point me to the relevant > >>documentation? > > > > > http://www.postgresql.org/docs/8.0/interactive/indexes-opclass.html > > Thanks! I didn't know this and I certainly didn't think it would be that > easy :) Well, it's not perfect. It requires a separate index from one for normal comparisons, so it's trading modification speed for LIKE lookup speed.