SQL Literals – How SQL Spec Specifies Literals with Decimals

datatypessql-standard

Different databases implicitly type numeric literals differently,

  • MySQL types 1.0 as decimal(2,1)
  • PostgreSQL types 1.0 as numeric

Which one of these is correct?

Best Answer

Both are correct and compliant on storage from literals. There are two types of numeric literals in the spec's eyes,

  • <exact numeric literal>
  • <approximate numeric literals> floating point, triggered by the use of Scientific Notation (with E or e)

For exact numeric literals (ENL),

22) The declared type of an ENL is an implementation-defined exact numeric type whose scale is the number of <digit>s to the right of the <period>. There shall be an exact numeric type capable of representing the value of ENL exactly.

For approximate numeric literals (ANL),

23) The declared type of an <approximate numeric literal> ANL is an implementation-defined approximate numeric type. The value of ANL shall not be greater than the maximum value nor less than the minimum value that can be represented by the approximate numeric types.

Meaning the type merely has to be able to hold the literal, the rest is left to the implementation.

Taken from the SQL 2011n spec

See also

Related Question