Обсуждение: 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
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.
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
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
-----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.
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
-----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.