Re: Real type with zero

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of David Johnston
Sent: Wednesday, June 29, 2011 11:51 AM
To: 'Grzegorz Jaśkiewicz'; condor(at)stz-bg(dot)com
Cc: pgsql-general(at)postgresql(dot)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 know whether it is truly sufficient. I would guess not since there may be cases where the known precision is less than the defined 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, at current, capture all possible values that have exactly 2 positions of scale. The only, practically meaningless, downside is if the upper-limit of precision ever were to change you would need to redefine all of these data types with the new precision to keep the same semantics.

Question: I store and retrieve (with some manipulation) currency (dollar) values often and use numeric to store them. I generally pick a reasonable precision, around 10 or so, and use 2 for the scale. Would specifying numeric(9998,2) result in 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 with a non-zero scale is meaningless so overloading doesn't seem to be that big an issue and this way you are not requiring the user to know the details of the implementation in order to pick the proper value for "MAX". Obviously polymorphism rules will not allow for numeric(scale) to be a valid construct since numeric(precision) would cause an ambiguity.

David J.