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)