Re: [BUGS] numerics lose scale and precision in views of unions

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> 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