2

I have created a column whose data_type is DOUBLE.

alter table my_table add column my_column double;

When I queried about this column in information_schema.COLUMNS table I found that the NUMERIC_SCALE for this column is NULL?

select DATA_TYPE,
NUMERIC_PRECISION,
NUMERIC_SCALE

from information_schema.columns

where table_schema = 'my_schema'
and table_name = 'my_table'
and column_name = 'my_column'

+-----------+-------------------+---------------+
| DATA_TYPE | NUMERIC_PRECISION | NUMERIC_SCALE |
+-----------+-------------------+---------------+
| double    |                22 |          NULL |
+-----------+-------------------+---------------+

Can someone explain this behavior?

1 Answer 1

3

Floating point numbers are exactly that -- there's no fixed position for the decimal separator (point), it can be anywhere, and specifying scale (i.e. the number of digits after the decimal separator) does not make sense.

Scale is used for the DECIMAL data type, a type with the fixed decimal separator, for which scale is significant and meaningful.

MySQL also allows a non-standard syntax for floating point numbers, where you can specify precision and scale, e.g. DOUBLE(10, 5). MySQL will round floating point values to that specification when storing them, and format them appropriately when returning query results.


Documentation link.

dbfiddle example.

3
  • I am trying to write a script to copy a table definition from MySQL to Redshift. The datatype in MySQL is of the type floating point. But the corresponding column in Redshift is expected to be a non-floating point. Hence the NULL value of NUMERIC_SCALE column is creating an issue. Maybe I should consider the range of values stored in that column before deciding on the values for PRECISION and SCALE in Redshift-table-definition. Is there any other workaround that you can suggest ? Commented Mar 23, 2019 at 16:43
  • You absolutely need to consider the range of existing values. You also need to consider what precision and scale your business requires.
    – mustaccio
    Commented Mar 23, 2019 at 17:59
  • @SubrataDas - If you were using float/double in MySQL for currency, that is the mistake that needs to be fixed.
    – Rick James
    Commented Apr 16, 2019 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.