Don Baccus <dhogaza@pacifier.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.
Youdon't add up phone numbers, though, do you?