SyntaxFix

[sql] How do I interpret precision and scale of a number in a database?

Precision of a number is the number of digits.

Scale of a number is the number of digits after the decimal point.

What is generally implied when setting precision and scale on field definition is that they represent maximum values.

Example, a decimal field defined with precision=5 and scale=2 would allow the following values:

  • 123.45 (p=5,s=2)
  • 12.34 (p=4,s=2)
  • 12345 (p=5,s=0)
  • 123.4 (p=4,s=1)
  • 0 (p=0,s=0)

The following values are not allowed or would cause a data loss:

  • 12.345 (p=5,s=3) => could be truncated into 12.35 (p=4,s=2)
  • 1234.56 (p=6,s=2) => could be truncated into 1234.6 (p=5,s=1)
  • 123.456 (p=6,s=3) => could be truncated into 123.46 (p=5,s=2)
  • 123450 (p=6,s=0) => out of range

Note that the range is generally defined by the precision: |value| < 10^p ...

Examples related to sql

• Passing multiple values for same variable in stored procedure • SQL permissions for roles • Generic XSLT Search and Replace template • Access And/Or exclusions • Pyspark: Filter dataframe based on multiple conditions • Subtracting 1 day from a timestamp date • PYODBC--Data source name not found and no default driver specified • select rows in sql with latest date for each ID repeated multiple times • ALTER TABLE DROP COLUMN failed because one or more objects access this column • Create Local SQL Server database

Examples related to database

• Implement specialization in ER diagram • phpMyAdmin - Error > Incorrect format parameter? • Authentication plugin 'caching_sha2_password' cannot be loaded • Room - Schema export directory is not provided to the annotation processor so we cannot export the schema • SQL Query Where Date = Today Minus 7 Days • MySQL Error: : 'Access denied for user 'root'@'localhost' • SQL Server date format yyyymmdd • How to create a foreign key in phpmyadmin • WooCommerce: Finding the products in database • TypeError: tuple indices must be integers, not str

Examples related to decimal

• Java and unlimited decimal places? • What are the parameters for the number Pipe - Angular 2 • Limit to 2 decimal places with a simple pipe • C++ - Decimal to binary converting • Using Math.round to round to one decimal place? • String to decimal conversion: dot separation instead of comma • Python: Remove division decimal • Converting Decimal to Binary Java • Check if decimal value is null • Remove useless zero digits from decimals in PHP

Examples related to scale

• Plotting with ggplot2: "Error: Discrete value supplied to continuous scale" on categorical y-axis • Understanding `scale` in R • simple Jquery hover enlarge • Adjusting the Xcode iPhone simulator scale and size • How to scale Docker containers in production • Scale the contents of a div by a percentage? • Fit Image in ImageButton in Android • Fit image into ImageView, keep aspect ratio and then resize ImageView to image dimensions? • How can I shrink the drawable on a button? • Auto Scale TextView Text to Fit within Bounds

Examples related to precision

• How do you round a double in Dart to a given degree of precision AFTER the decimal point? • Show two digits after decimal point in c++ • Get DateTime.Now with milliseconds precision • How to convert milliseconds to seconds with precision • Dividing two integers to produce a float result • Double precision - decimal places • Changing precision of numeric column in Oracle • Double precision floating values in Python? • JavaScript displaying a float to 2 decimal places • What is the difference between float and double?