Re: Best data type to use for sales tax percent

On Oct 9, 2009, at 12:14 PM, Mike Christensen wrote:
> I will definitely create a domain for this (and probably for some
> other types in my app since I now know about this). However, is the
> CHECK really necessary? A numeric(5,5) already has a maximum value of
> 10^0, so it would already create an overflow error if you set it
> higher. Is there an advantage of using the CHECK constraint? Perhaps
> this is faster or doesn't lock the row on update or something?
>
> Just to point out, NUMERIC and DECIMAL are one and the same yes?

Sorry, NUMERIC is correct; DECIMAL was my brain misfiring.

Since NUMERICs can go negative regardless of scale, the CHECK is handy
to make sure that you do not insert a negative number. (This is
assuming, of course, that a sales tax rate can't actually be negative
for some reason.) It's not required, of course, but it's always
advisable to put as much reasonable data checking into the database as
you can.

Peter Eisentraut's suggestion of just not putting a scale or precision
on the type at all and using CHECK to validate the values is also a
fine way of handling it.
--
-- Christophe Pettus
xof(at)thebuild(dot)com