Re: Advice - indexing on varchar fields where only last x characters known

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.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services