From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | 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:39:02 |
Message-ID: | 20131017183902.GA4943@eldon.alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Flower | 2013-10-17 18:43:06 | Re: Advice - indexing on varchar fields where only last x characters known |
Previous Message | Gavin Flower | 2013-10-17 18:27:05 | Re: Advice - indexing on varchar fields where only last x characters known |