From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Advice - indexing on varchar fields where only last x characters
 known
Date: Thu, 17 Oct 2013 12:20:44 +0100
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201310171220.44550.gary.stainburn@ringways.co.uk>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

I have a problem with a field that appears on a number of my tables.

The field is the Vehicle Identification Number. Every vehicle has one and it 
uniquely identifies that vehicle.

Traditionally this was a 11 character string but a number of years ago was 
extended to 17 characters by adding a 6 character prefix.


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.

However, it means that every time I'm trying to connect various tables up 
using foreign keys or doing searches I have to make allowences for this which 
means I'm using things like substring, like, regex etc. all of which are very 
slow. 

Can anyone suggest a better / more efficient way of handling these.

Gary


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