On Aug 28, 2008, at 12:27 PM, Tino Wildenhain wrote:
Hi Bill,
Bill wrote:
The SQL database servers I have worked with cannot use and index
for a SELECT of the form
SELECT * FROM ATABLE
WHERE AFIELD LIKE ?
because there is no way to know the location of the wild card until
the parameter value is known. InterBase and Firebird allow
SELECT * FROM ATABLE
WHERE AFIELD STARTING WITH ?
which is equivalent to LIKE 'ABC%' and will use an index on
AFIELD. Is there a similar syntax in PostgreSQL?
Yes, its actually: LIKE 'ABC%' and it will use an index.
I think Bill's point is that the planner can't take advantage of that
at the time it's planning the query unless it has the string at that
point. Something like "STARTING WITH" could be used with prepared
statements too.
Cheers,
Steve