Re: Index on substring?

On Thu, 12 Oct 2000, Tom Lane wrote:

> Jeff Eckermann <jeckermann(at)verio(dot)net> writes:
> > extracts=# create index c_namesum_i on customers (substr(bill_company,1,5));
> > ERROR: parser: parse error at or near "1"
>
> The functional-index syntax only allows a function name applied to
> simple column names.
>
> You can work around this by defining a function that handles any
> additional computation needed, eg,

I can't help but think this is a table design issue. Maybe not fully
normalized or needs to be de-normalized some. If the index is part of a
continuing need I'd suggest adding a column made up of the substring and
indexing on it instead. If the design isn't too far along review the
bill_company attribute (column) and see it it should be two columns.
It's always been easier for me to tie pieces together (views) than to
break them out of chunks.

Rod
--
Roderick A. Anderson
raanders(at)altoplanos(dot)net Altoplanos Information Systems, Inc.
Voice: 208.765.6149 212 S. 11th Street, Suite 5
FAX: 208.664.5299 Coeur d'Alene, ID 83814