* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > Makes me curious if it really makes sense to keep trailing zeros... > > AFAIR we consider them mainly as a display artifact. An application > that's declared a column as numeric(7,2) is likely to expect to see > exactly two digits after the decimal point.
Hmm. I should have mentioned this previously (since I was thinking about it at the time...) but this display artifact is unfortunately not without consequences. I'm about 80% sure that having the scale too large (as in, larger than about 6 or 7 decimal places) breaks MS Access using ODBC. It complains about not being able to represent the number (even though it's just trailing zeros). It might be possible to handle that in the ODBC driver but I don't think it'd be very clean (considering you would want to fail cases where it's not just trailing zeros). This was using just a straight-up 'numeric' data type though. Perhaps for that case we could drop the unnecessary zeros? I can understand having them there when a specific scale is specified (I suppose...) but when there isn't a specific scale given any application would have to deal with the variability in the number of digits after the decimal point anyway. > > Either 1.0 and 1.00 are > > the same thing (and thus should be displayed the same way), or they > > aren't (in which case they should be treated distinctly in, eg, a > > 'select distinct' clause). > > Consistency has never been SQL's strong point ;-) Indeed. I think my suggestion above would be at least a half-step towards consistancy without breaking things. I *think* this would also mean that we'd always have either a fixed number of decimal places (scale specified), or no trailing zeros. This would, in fact, be awfully nice for me since I wouldn't have to deal with things like: 78.4 2.3625 4.1666675000000000 16.6666675000000000 0.83333250000000000000 where I then get to do some *very* ugly magic to find the records with the extra zeros off on the end and truncate them (think cast to text and then use a regexp, not fun at all...). Unfortunately round(a,b) <> a doesn't work so hot in these cases where you do want the precision just not the extra zeros off on the end. Thanks, Stephen
signature.asc
Description: Digital signature