Re: MySQL search query is not executing in Postgres DB

On Feb 18, 2012, at 1:43 PM, Christopher Browne wrote:

> On Sat, Feb 18, 2012 at 4:12 PM, Don Baccus <dhogaza@pacifier.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?

----
Don Baccus
http://donb.photo.net
http://birdnotes.net
http://openacs.org