Search code examples
sqlmariadbsql-insertcreate-table

Small number out of DOUBLE range

I created a table on a mariaDB with the following definition. Note the longitude and latitude fields.

Create Table geo_data (
    geo_data_id int NOT NULL AUTO_INCREMENT,
    place_id int NOT NULL,
    longitude DOUBLE(18,18) SIGNED,
    latitude DOUBLE(18,18) SIGNED,
    Primary Key (geo_data_id),
    Foreign Key (place_id) References place (place_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

When I try to insert data into the geo_data table using

Insert into geo_data (place_id, longitude, latitude) Values (1, 1.2, 3.4);

I receive the following error message:

Error: ER_WARN_DATA_OUT_OF_RANGE: Out of range value for column 'longitude' at row 1

I guess I am missing something here, since I don't believe 1.2 could in any way be out of range of a Double(18,18). So what on earth is going on here?

Solution

  • Your column is defined as DOUBLE(18,18). The first number is the scale (the total number of digits in the whole number, including decimals); the second is the precision (the number of decimal positions).

    Giving the same value to both the scale and precision means that your value cannot be greater than 1 (all 18 digits are decimals).

    You want to decrease the precision to something smaller in order to leave room for non-decimal digits, like: DOUBLE(18, 6), which gives you 12 non-decimal positions.