Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

postgresql - Can the Postgres data type NUMERIC store signed values?

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

You can certainly use the arbitrary precision type numeric with a precision of 5 and a scale of 1, just like @Simon commented, but without the syntax error. Use a comma(,) instead of the 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.

Details for numeric types in the manual.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...