Can the Postgres NUMERIC data type store signed values?

Question

In PostgreSQL, I would like to keep the signed values -999.9 - 9999.9 . Can I use numeric(5.1) for this?

Or what type should I use?

Answer 1

You can use arbitrary numeric precision type with precision 5 and scale 1, as @Simon commented , but without a syntax error. Use a comma ( , ) instead of a period ( . ) In a 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 take into account the allowable maximum of significant digits ( precision ).
If you do not need to limit the length, just use numeric .
If you need to provide a minimum and maximum value, add a control constraint :

 CHECK (nr_column BETWEEN -999.9 AND 9999.9) 

numeric accurately saves your number. If you do not need absolute precision and tiny rounding errors, no problem, you can also use one of the floating point types double precision ( float8 ) or real ( float4 ).

Or, since you only allow one fractional decimal digit, you can multiply by 10 and use integer , which will be the most efficient storage: 4 bytes, no rounding errors and the fastest processing. Just use and write down the number correctly.

Information on numeric types in the manual.