Re: MySQL search query is not executing in Postgres DB

On Sat, Feb 18, 2012 at 5:34 PM, Don Baccus <dhogaza@pacifier.com> wrote:
>
> On Feb 18, 2012, at 1:43 PM, Christopher Browne wrote:
>> 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!

Yes, and I was intentionally treating this as an oddity.

> Your example is actually a good argument for storing account ids as text, because '0000' like '0%' *will* match.

Absolutely.

The trouble is that if you use the term "account NUMBER" enough times,
some portion of people will think that it's a number in the sense that
it should be meaningful to add and subtract against them.

> "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?

I think it's not quite right to treat it as "how is PG supposed to
know."  The problem is a bit more abstract; it occurs without having a
database involved.

The notion that the ranges (3), (30-39), (300-399), and (3000-3999)
ought to be considered connected together in the account number
classification is what seems crazy to me.  But that's what "account
number starts with a 3" could be expected to imply.

At any rate, yes, this is liable to point the Lone Ranger towards
solutions that involve him not riding off into the sunset!
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"