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

Question

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?

GMB · Accepted Answer · 2020-05-22 22:50:38Z

33

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.

answered May 22, 2020 at 22:50

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zach G Over a year ago

Thanks! so precision is # of digits, scale is # of decimal digits.

GMB Over a year ago

@ZachG: yes, exactly.

Kamil Gosciminski · Accepted Answer · 2020-05-22 22:52:14Z

3

NUMERIC(4,1) will do, but this will also accept decimals such as:

55.0
26.3
978.1

This is because precision declares maximum of digits a number can hold and scale relates to decimal part. You would also need to validate your input.

If you wish to do this at database level, you could use CHECK column_name <= 100.0. Such check could also be extended to accept only numbers with .0 and .5 decimal part (see modulo operator).

answered May 22, 2020 at 22:52

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related