Search code examples
sqlpostgresqlsqldatatypes

What are precision and scale for numeric data types in Postgres?

The documentation doesn't have examples.

https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-NUMERIC-TABLE

NUMERIC(precision, scale)

I want to use the smallest amount of space to save a positive number that will be at most 100 and I need to accept decimal increments of 0.5

What precision and scale should I use in this case?

Solution

  • Use numeric(4, 1).

    This gives you a total of 4 digits maximum (including the decimal part), with 1 digit reserved for the decimals, so this would store numbers up until 999.9.

    If you can live with numbers that are not greater than 99.9, then numeric(3, 1) is fine.