Date: Thu, 17 Oct 2013 15:39:02 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: Advice - indexing on varchar fields where only last x
 characters known
Message-ID: <20131017183902.GA4943@eldon.alvh.no-ip.org>
References: <201310171220.44550.gary.stainburn@ringways.co.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <201310171220.44550.gary.stainburn@ringways.co.uk>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql