Thread: Real type with zero

Real type with zero

From
Condor
Date:
Hello,
how I can tell my postgresql to store last zero of real type ? I put
value 2.30 and when I select that column i see 2.3 without zero.


--
Regards,
Condor

Re: Real type with zero

From
Grzegorz Jaśkiewicz
Date:
what you probably looking for is formatting the output into a string.
Postgresql will store it as 2.3, because that is what 2.30 is anyway.
Its up to you to format it before passing it on to the user/business
logic/whatever.

Re: Real type with zero

From
Radoslaw Smogura
Date:
Your question may suggest you are more interested in storing value, as decimal not real, it's more secure to use this
wayfor moneys, but even with decimal your trailing zeros may be removed.

Regards,
Radoslaw Smogura 

-----Original Message-----
From: Condor
Sent: 29 czerwca 2011 13:24
To: pgsql-general@postgresql.org
Subject: [GENERAL] Real type with zero

Hello,
how I can tell my postgresql to store last zero of real type ? I put
value 2.30 and when I select that column i see 2.3 without zero.


--
Regards,
Condor

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



Re: Real type with zero

From
Craig Ringer
Date:
On 29/06/2011 7:24 PM, Condor wrote:
> Hello,
> how I can tell my postgresql to store last zero of real type ? I put
> value 2.30 and when I select that column i see 2.3 without zero.

The real data type is an IEEE 754 floating point number. See:

http://en.wikipedia.org/wiki/Floating_point
http://steve.hollasch.net/cgindex/coding/ieeefloat.html

It doesn't store any information about formatting or layout. If you want
to retain that information, you'll need to use NUMERIC or just store
your numbers as formatted strings. Note that NUMERIC doesn't store error
ranges and its formatting isn't preserved by most arithmetic operations;
it's not a full scientific error-bounded numeric type.

regress=> SELECT '4401.00100'::numeric;
   numeric
------------
  4401.00100
(1 row)

regress=> SELECT '4401.00100'::float;
   float8
----------
  4401.001
(1 row)

--
Craig Ringer

Re: Real type with zero

From
"David Johnston"
Date:
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Grzegorz Jaskiewicz
Sent: Wednesday, June 29, 2011 7:44 AM
To: condor@stz-bg.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Real type with zero

what you probably looking for is formatting the output into a string.
Postgresql will store it as 2.3, because that is what 2.30 is anyway.
Its up to you to format it before passing it on to the user/business logic/whatever.



I presume (and even if not) the OP is looking to keep the known precision of the value.  If I look at 2.3 I do not know
whetherI have precision of measurement only to the tenths or whether I had higher precision but all positions beyond
thetenths are zero. 

Aside from storing the "true" precision in a separate integer field what solution is there is this situation.  I guess
defining"numeric(S,P)" works although I haven't done much actual work with "precision" in the database and so I do not
knowwhether it is truly sufficient.  I would guess not since there may be cases where the known precision is less than
thedefined precision and so the numeric(S,P) data type will over specify the precision in those cases. 

This is beginning to sound like a varchar(n) versus text argument...

David J.



Re: Real type with zero

From
Scott Ribe
Date:
On Jun 29, 2011, at 9:50 AM, David Johnston wrote:

> Aside from storing the "true" precision in a separate integer field what solution is there is this situation.

I think the only other way would be a custom data type encapsulating those 2 bits of info. Which might be the best
solution,since if you really need to maintain info about the significant digits of measurements, you need that to carry
throughproperly in calculations with those numbers. 

OTOH, it's possible to have that info in a separate field, and leave with clients the responsibility for correct
calculations...

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





Re: Real type with zero

From
"David Johnston"
Date:
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Johnston
Sent: Wednesday, June 29, 2011 11:51 AM
To: 'Grzegorz Jaśkiewicz'; condor@stz-bg.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Real type with zero


Aside from storing the "true" precision in a separate integer field what solution is there is this situation.  I guess
defining"numeric(S,P)" works although I haven't done much actual work with "precision" in the database and so I do not
knowwhether it is truly sufficient.  I would guess not since there may be cases where the known precision is less than
thedefined precision and so the numeric(S,P) data type will over specify the precision in those cases. 

This is beginning to sound like a varchar(n) versus text argument...

David J.


>>>>>>>>>>>>>>>>>>>>..

Now that I've re-read the section on numeric I have a couple of further points.

1) I indeed reversed scale and precision; but context should make that obvious.
2) You can specify a known precision (and default scale of 0) but cannot specify a specific known scale with unbounded
precision. From the documentation I presume you can specify "numeric(1000-s, s); where 's' is the desired scale" and,
atcurrent, capture all possible values that have exactly 2 positions of scale.  The only, practically meaningless,
downsideis if the upper-limit of precision ever were to change you would need to redefine all of these data types with
thenew precision to keep the same semantics. 

Question: I store and retrieve (with some manipulation) currency (dollar) values often and use numeric to store them.
Igenerally pick a reasonable precision, around 10 or so, and use 2 for the scale.  Would specifying numeric(9998,2)
resultin identical performance and storage - for a given value - compared to storing that value in a numeric(10,2)?  

Also, is there any reason why "numeric(0,n)" couldn't be used as a synonym for "numeric(MAX-n, n)"?  Zero precision
witha non-zero scale is meaningless so overloading doesn't seem to be that big an issue and this way you are not
requiringthe user to know the details of the implementation in order to pick the proper value for "MAX".  Obviously
polymorphismrules will not allow for numeric(scale) to be a valid construct since numeric(precision) would cause an
ambiguity.

David J.