Re: MySQL search query is not executing in Postgres DB

Don Baccus <dhogaza(at)pacifier(dot)com> writes:
>> A hierarchy like the following is perfectly logical:
>> - 0000 to 0999 :: Cash accounts [1]
>
> Your example is actually a good argument for storing account ids as
> text, because '0000' like '0%' *will* match.

FWIW, I too think that if you want to process your integers as text for
some operations (LIKE) and as integer for some others, you'd better do
the casting explicitly.

In the worked-out example Christopher has been proposing, just alter the
column type to text and be done, I can't see summing up or whatever int
arithmetic usage being done on those general ledger account numbers. Use
a domain (well a CHECK constraint really) to tight things down.

As for lpad(), that's a function working on text that returns text, so
having a variant that accepts integers would not be confusing. Then
again, why aren't you using to_char() if processing integers?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

PS: having worked on telephone number prefix indexing and processing
them as text, I might have a biased opinion. You don't add up phone
numbers, though, do you?