This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
A floating point constant is similar to an integer constant, but it is used to represent decimal values as well as whole integers. These are required whenever such a floating point value must be represented literally within a SQL statement.
A floating point constant can be interpretted by PostgreSQL in several forms, as shown in this list where ## represents one or more numbers:
##.##
## e[+-] ##
[ ## ]. ## [e[+-] ##
## .[ ## ][e[+-] ##
In the first form, there must be at least one digit before or after the decimal point for PostgreSQL to recognize the value as a floating point constant versus an integer constant. The other options involve having at least one digit before or after an exponent clause , denoted by the e in the list. The presence of either the decimal point, the exponent clause, or both, distinguishes an integer constant from a floating point. There are no other possible characters or spaces within a floating point constant.
Each of these valid formats is represented in Example 3-8 through a simple SQL SELECT statement illustrating a variety of floating point conventions.
Example 3-8. Valid Floating Point Values
booktown=# SELECT .04 AS small_float, booktown-# -16.63 AS negative_float, booktown-# 4e3 AS exponential_float, booktown-# 6.1e-2 AS negative_exponent; small_float | negative_float | exponential_float | negative_exponent -------------+----------------+-------------------+------------------- 0.04 | -16.63 | 4000 | 0.061 (1 row)
 
Continue to: