The numeric string data type is a virtual type that exists only when numeric data types are compared directly with character data. If a comparison is requested between these two classes of data, the character data is examined to see if it is numeric in form. If it is, then the comparison is performed as though both were numeric. If the data is not numeric, the result will be such that all numbers collate before all non-numbers. For example:

–100 < ‘-9’ < ‘0.01’ < 1 < ‘1.1e1’ < ‘one’ < ‘three’ < ‘two’

The numeric conversion is performed using float8 precision and leading and trailing spaces are ignored.

The data types considered as numeric are all the integer types, all the float types, and decimal. Money is not treated in this manner because it has its own character data compatibility.

To be deemed numeric, a character data value must contain only one number, which can be in integer, decimal, float, or scientific notation form. All the character data types are checked for numeric content except the long variants, which are treated as non-numeric.