PostgreSQL: what is the difference between float(1) and float(24)?

Question

I am having a hard time understanding the precision parameter p for float(p) in PostgreSQL. For example, float(1) and float(24) seem to be exactly the same to me.

Can anyone provide me with some examples of their differences, please?

Answer 1

It's in the manual:

PostgreSQL also supports the SQL-standard notations float and float(p) for specifying inexact numeric types. Here, p specifies the minimum acceptable precision in binary digits. PostgreSQL accepts float(1) to float(24) as selecting the real type, while float(25) to float(53) select double precision. Values of p outside the allowed range draw an error. float with no precision specified is taken to mean double precision.

However, the key thing here is that it specifies the minimum acceptable precision. PostgreSQL uses this to select the underlying data type (float4 or float8) that meets the requirement.

regress=> \x
Expanded display is on.
regress=> SELECT
        '1.123456789123456789'::float,
        '1.123456789123456789'::double precision,
        '1.123456789123456789'::float(1),
        '1.123456789123456789'::float(2),
        '1.123456789123456789'::float(24),                                                                                                                                     
        '1.123456789123456789'::float(48);                                                                                                                                     
-[ RECORD 1 ]------------                                                                                                                                                      
float8 | 1.12345678912346
float8 | 1.12345678912346
float4 | 1.12346
float4 | 1.12346
float4 | 1.12346
float8 | 1.12345678912346

You can use pg_typeof to confirm the type selections.

Effectively it's just there to support the standard syntax and gets translated into PostgreSQL's dialect - through selection of appropriate types - where it's used.