Re: Newbie question about BIGINT and INT in the same table
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Sun, 27 Oct 2019 22:09:00 -0400
Message-ID: <qp5ij4$9l$1_at_jstuckle.eternal-september.org>
>
> 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.
>
Date: Sun, 27 Oct 2019 22:09:00 -0400
Message-ID: <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:
>
>> On 10/27/2019 6:09 AM, ^Bart wrote: >>>> You use the size of variable you need. if the values in the age >>>> column are really going to exceed the time to the heat death of the >>>> universe, use a bigint >>> >>> Ok, now I understood, I could use for the age also VARCHAR(3) because >>> it means I can insert in a row till three numbers (I know VARCHAR is >>> for string but I could use also to insert numbers!) but the column's >>> value is "infinity" so I could have a column of millions and millions >>> of values but these values are till three items! >>> >>> The mistake was to think to the columns and not to the value of the >>> row! >>> >>> Thanks! >>> ^Bart >> >> 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.
But there are better ways than using a decimal type - i.e. triggers to limit the range of values.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Mon Oct 28 2019 - 03:09:00 CET