Table "routes"
Column | Type | Modifiers
-------------+-------------------+-----------
route | character varying |
route
------------
1
1234
1234567
12345678
123456789
555555555
(6 rows)
Objective:
Return all rows where 'route' matches any or all of the search text '12345678901'
starting at leftmost character
Examples:
'12345678901' would return rows 1 through 5
'555555555' would return only row 6
Working queries:
SELECT route FROM routes WHERE '12345678901' LIKE route || '%';
SELECT route FROM routes WHERE route = SUBSTR('12345678901', 1, LENGTH(route));
Problem:
Neither query is indexable (as far as I know) with base PostgreSQL 8.1. I'm
looking for an indexable solution as the routes table has, on avg, 500,000 rows,
not the 6 I've shown in the example. If a redesign is in order, or using another
datatype is preferable, anything... suggestions are always welcome.
Thanks,
James Saunders