Re: Best data type to use for sales tax percent

From: Christophe Pettus <xof(at)thebuild(dot)com>
To: "pgsql-general(at)postgresql(dot)org mailing list" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Best data type to use for sales tax percent
Date: 2009-10-09 19:36:04
Message-ID: 2564D1DC-808A-47F1-856A-E17E82220474@thebuild.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Mike Diehl 2009-10-09 19:53:56 Stuck vacuum...
Previous Message Peter Eisentraut 2009-10-09 19:18:17 Re: Best data type to use for sales tax percent