agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
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
Message-ID: <201310171220.44550.gary.stainburn@ringways.co.uk> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (11+ messages) latest in thread
Message-ID: <201310171220.44550.gary.stainburn@ringways.co.uk>
Permalink: ../201310171220.44550.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201310171220.44550.gary.stainburn@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: Advice - indexing on varchar fields where only last x characters known
In-Reply-To: <201310171220.44550.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox