I think a formating mode where only the relevant digits are written
to the output would be great as an alternative to the discussed 
fixed formatting strings. In this context i think of 'relevant' 
as in the following:

'Output as few characters as possible but ensure that scanf is 
still able to rebuild the binary reprressentation of the floating 
point number exactly.'

To make this happen we would need to compute a seperate formatting
string for each floating point value:

E.g. if the binary value is exactly '1.00000E00' then we just
write '1' to the output, because the rest is just 'ASCII noise'
and not neccessary for rebuilding the identical binary value for
the given floating point value.

The advantage would be, that we only generate as much ASCII data
as absolutly neccessary to rebuild the original data exactly.
At least this is what I would expect from pg_dump.

robert schrem

Wed, 21 Feb 2001 10:19:41 +0100
Robert Schrem <Robert.Schrem@WiredMinds.de>
Re: floating point representation
At 10:19 21/02/01 +0100, Robert Schrem wrote:
>The advantage would be, that we only generate as much ASCII data
>as absolutly neccessary to rebuild the original data exactly.
>At least this is what I would expect from pg_dump.

pg_dump is only one side of thre problem, but the simplest solution might
be to add an option to dump the hex mantissa, exponent & sign. This should
be low-cost and an exact representation of the machine version of the number.

The other issues, like what is sent to psql & via interfaces like odbc
(currently text) should be application/DBA based and setable on a
per-attribute basis. eg. some applications want 1.0000 because the data
came from a piece of hardware with a know error, and 1.0000 means 1.0000+/-
0.00005 etc. Maybe this is just an argument for a new 'number with error'
type...


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 21 Feb 2001 21:13:23 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Re: floating point representation
On Wed, 21 Feb 2001, you wrote:
> At 10:19 21/02/01 +0100, Robert Schrem wrote:
> >The advantage would be, that we only generate as much ASCII data
> >as absolutly neccessary to rebuild the original data exactly.
> >At least this is what I would expect from pg_dump.
> 
> pg_dump is only one side of thre problem, but the simplest solution might
> be to add an option to dump the hex mantissa, exponent & sign. This should
> be low-cost and an exact representation of the machine version of the number.

The hex dumps should be done in a machine independant way - I think
that's what you ment when stating mantissa, exponent & sign seperatly,
right? I think this whould be a good solution...

> The other issues, like what is sent to psql & via interfaces like odbc
> (currently text) should be application/DBA based and setable on a
> per-attribute basis. 

You think of an additional tag in a CREATE TABLE instruction like

CREATE TABLE temperature (
    id             id,
    messure_time   timestamp default now() formatted as "hhmmmdd",
    value          float formatted as "%3.2f"
);

or maybe

CREATE TABLE temperature (
    id                       id,
    messure_time("hhmmmdd")  timestamp default now(),
    value("%3.2f")           float
);

or is there something in SQL99 ?

> eg. some applications want 1.0000 because the data
> came from a piece of hardware with a know error, and 1.0000 means 1.0000+/-
> 0.00005 etc. Maybe this is just an argument for a new 'number with error'
> type...

I think a float value in a database column has no known error 
range and therefore we should not care about the 'physical' error 
of a value in this context. Just think of a computed column in a 
VIEW - how can we know for shure how percise such a result is if
we don't have any additional information about the messurement 
errors of all operands (or constants) involved.

If you would introduce a new type - 'number with error' - this 
would be totally different and a big contribution to the solution 
of this. Then you can also handle errors like 1.0000+/-0.00002 
percisely - witch you can't by only formatting 1.0000.

robert schrem








Wed, 21 Feb 2001 12:34:04 +0100
Robert Schrem <Robert.Schrem@WiredMinds.de>
Re: Re: floating point representation
Philip Warner <pjw@rhyme.com.au> writes:
> The other issues, like what is sent to psql & via interfaces like odbc
> (currently text) should be application/DBA based and setable on a
> per-attribute basis. eg. some applications want 1.0000 because the data
> came from a piece of hardware with a know error, and 1.0000 means 1.0000+/-
> 0.00005 etc. Maybe this is just an argument for a new 'number with error'
> type...

FWIW, there is a number-with-error type in contrib/seg ...

			regards, tom lane

Wed, 21 Feb 2001 10:19:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: floating point representation 