| From: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
|---|---|
| To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
| Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Re: Advice - indexing on varchar fields where only last x characters known |
| Date: | 2013-10-17 18:43:06 |
| Message-ID: | 52602FBA.50303@archidevsys.co.nz |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
On 18/10/13 07:39, Alvaro Herrera wrote:
> Gary Stainburn wrote:
>
>> The problem that I have is that these VIN numbers are provided by a number of
>> data systems including manufacturer feeds, logistics companies as well as
>> internal systems. Some use the full 17 character string while others only use
>> the last 11.
>>
>> On top of this, my users are used to only having to type the last 6 characters
>> for speed and usability reasons.
> Try creating an index on reverse(vin) and using the same function in
> queries; you can put a % at the end of the sought-for literal to match
> suffixes. That works quite well and is very simple to implement.
>
That is extremely cunning,
and obvious in retrospect! :-)
Cheers,
Gavin
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Johnston | 2013-10-17 19:16:30 | Re: Advice - indexing on varchar fields where only last x characters known |
| Previous Message | Alvaro Herrera | 2013-10-17 18:39:02 | Re: Advice - indexing on varchar fields where only last x characters known |