Re: MySQL search query is not executing in Postgres DB

On 02/17/2012 02:28 PM, Christopher Browne wrote:
> On Fri, Feb 17, 2012 at 1:21 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>> Assuming we had the cast, What would "intval like '1%'" mean? You're going
>> to match 1, 10..19, 100..199, 1000..1999 ...
>>
>> Now maybe there's a good use for such a test, but I'm have a VERY hard time
>> imagining what it might be.
> Well, I can readily see someone encoding parts of their application
> into this sort of encoding, so that, for instance, all customer
> numbers beginning with "1" are deemed to be "internal accounts."
>
> It's a pretty terrible approach to encoding data for an application;
> it leads to stuff like the "oops, once they have generated 30,000
> invoices, the system reaches doomsday and can't work anymore."
> http://thedailywtf.com/Articles/Ive-Got-Your-Number.aspx
>
> But nothing prevents users from designing their applications to encode
> information in their ID prefixes.
>
> And I have *zero* confidence that for PostgreSQL to rule out "LIKE
> '1%'" is preventing those designs from getting built...
>
> When confronted by a difficult problem, solve it by reducing it to the
> question, "How would the Lone Ranger handle this?"
>

Strings of digits used that way should not be stored in numeric fields
at all, IMNSHO, just as zip codes and phone numbers should not be. They
should be text in the first place, and if the OP had done that he
wouldn't have had any difficulty about this. I hope that's what the Lone
Ranger would do ...

cheers

andrew