Re: Newbie question about BIGINT and INT in the same table

From: Lyle H. Gray <lylefitzw_at_no.spam.gmail.com.invalid>
Date: Mon, 28 Oct 2019 08:17:21 -0500
Message-ID: <XnsAAF65E7DFEC04graynoibisspamcsumas_at_216.166.97.131>

Jerry Stuckle <jstucklex_at_attglobal.net> wrote in [Quoted] news:qp5ij4$9l$1_at_jstuckle.eternal-september.org:

> On 10/27/2019 11:47 AM, Lyle H. Gray wrote:

>> Jerry Stuckle <jstucklex_at_attglobal.net> wrote in
>> news:qp4bqs$q6t$1_at_jstuckle.eternal-september.org:
>>

>>> You do NOT want to store numeric data as strings.  For one thing,
>>> sorting will be crap. '2' will be larger than '15'.

>>
>> Agreed. If you want to limit the value size with a length, rather a
>> specification of storage used (e.g. as TINYINT, SMALLINT, INT,
>> BIGINT), then use a fixed decimal datatype (NUMERIC() for whole
>> numbers, DECIMAL() otherwise).
>>
>> If you _do_ use VARCHAR() for storage of numbers that you want to be
>> able to sort on, you would need to have leading zeroes (e.g. '002'
>> instead of '2'). That's usually adding complexity that you don't
>> need.
>>
>
> Not to mention you'll also have to search for '002' instead of 2.

Good point.

> But there are better ways than using a decimal type - i.e. triggers to > limit the range of values.

[Quoted] You and I have different definitions of "better". :D

Perhaps a CHECK CONSTRAINT to limit the values, especially if you want to insure that they're positive values (which you would for ID values). Received on Mon Oct 28 2019 - 14:17:21 CET