RE: Index on substring?

Thanks for the input.
In this case, we are not talking about persistent tables: they were
specially created for a particular purpose, and will be trashed again
afterwards.
To explain a little: I am tying together disparate customer databases that
have no common identifiers, other than the customer names and addresses.
These are subject to the usual variations in rendering, so direct name
comparisons fail far too often, although the two compared records refer to
the same customer. I have found that comparison on the first few characters
of the name gives good (enough) results, thus my interest in the substring.

> -----Original Message-----
> From: Roderick A. Anderson [SMTP:raanders(at)altoplanos(dot)net]
> Sent: Thursday, October 12, 2000 9:53 AM
> To: 'pgsql-general(at)postgresql(dot)org'
> Subject: Re: [GENERAL] 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