Re: MySQL search query is not executing in Postgres DB

On 02/18/2012 05:34 PM, Don Baccus wrote:
> On Feb 18, 2012, at 1:43 PM, Christopher Browne wrote:
>
>> On Sat, Feb 18, 2012 at 4:12 PM, Don Baccus<dhogaza(at)pacifier(dot)com> wrote:
>>> On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote:
>>>> Where first_name is string the queries above have very different
>>>> behaviour in MySQL. The first does a full table scan and coerces
>>>> first_name to an integer (so '5adfs' -> 5)
>>> Oh my, I can't wait to see someone rise to the defense of *this* behavior!
>> I can see a use, albeit a clumsy one, to the notion of looking for values
>> WHERE integer_id_column like '1%'
>>
>> It's entirely common for companies to organize general ledger account
>> numbers by having numeric prefixes that are somewhat meaningful.
>>
>> A hierarchy like the following is perfectly logical:
>> - 0000 to 0999 :: Cash accounts [1]
> I asked earlier if anyone would expect 01 like '0%' to match …
>
> Apparently so!
>
> Your example is actually a good argument for storing account ids as text, because '0000' like '0%' *will* match.
>
> "I'd think it nearly insane if someone was expecting '3%' to match not
> only the '3000 thru 3999' series, but also '300 to 399' and "30 to 39"
> and "3"."
>
> How is PG supposed to know that integers compared to strings are always to be padded out to precisely 4 digits?
>

By this point the Lone Ranger has committed suicide.

cheers

andrew