In PostgreSQL, I would like to store signed values -999.9 - 9999.9.
Can I use numeric(5.1) for this?
Or what type should I use?
1 Answer
You can certainly use the arbitrary precision type numeric with a precision of 5 and a scale of 1, just like @Simon commented, but with comma(,) instead of a dot (.) in the type modifier:
SELECT numeric(5,1) '-999.9' AS nr_lower
, numeric(5,1) '9999.9' AS nr_upper;
nr_lower | nr_upper
----------+----------
-999.9 | 9999.9
The minus sign and the dot in the string literal do not count against the allowed maximum of significant digits (precision).
If you don't need to restrict the length, just use numeric.
If you need to enforce minimum and maximum, add a check constraint:
CHECK (nr_column BETWEEN -999.9 AND 9999.9)
numeric stores your number exactly. If you don't need the absolute precision and tiny rounding errors are no problem, you might also use one of the floating point types double precision (float8) or real (float4).
Or, since you only allow a single fractional decimal digit, you can multiply by 10 and use integer, which would be the most efficient storage: 4 bytes, no rounding errors and fastest processing. Just use and document the number properly.
answered Feb 13, 2016 at 2:21
Erwin Brandstetter
669k160 gold badges1.2k silver badges1.3k bronze badges
3 Comments
mouwsy
Regarding your last paragraph: This floating point guide doesn't recommend to store numbers as integer as a sort of “poor man’s decimal type” and only format the output as decimal numbers, see reasons in the link.
Erwin Brandstetter
@mouwsy: The author lists four talking points. None of them is compelling for Postgres in a general sense. Always pick the right data type for the use case.
mouwsy
I disagree and just want to recapitulate some reasons why the approach you mention is not recommended: It’s more work, buggy, and non-standard.
numeric(5.1)will be OK. See the documentation for details.numericis part of the sql standard, and yes, it's signed.