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?


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

| improve this answer | |
  • 1
    So, to summarise, both Postgresql and MySql are compliant. – Jasen Jun 14 '18 at 8:59
  • @Jasen updated to be more explicit about that. =) – Evan Carroll Jun 14 '18 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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