Re: Real type with zero

-----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.