Re: type-casting and LIKE queries

On Fri, 14 Mar 2003, valerian wrote:

> I have an indexed column called home_phone, which is of type bigint.
> How can I search this column efficiently, using LIKE queries?  For

I don't think you do. LIKE is defined on string types in any case, so even
if you could convert it to a bigint, it's not going to do what you seem
to want (using an index on a bigint).

You could make a function that got say the top 3 digits of a number and
use it in a functional index.

However, for what you're doing, that might not be safe anyway (nor would
like) unless you're certain that your input format is fixed or at least
requires a consistent number of digits (what happens if someone doesn't
put an area code and you expect one, or does and you don't).

> example:
>
>    test=> SELECT id FROM user WHERE home_phone LIKE '407%'::bigint;
>    ERROR:  Bad int8 external representation "407%"
>
> Obviously, that's not correct, but I'm confused as to how the % and ?
> characters can be used along with type-casting.  Or can they?