PostgreSQL: Advantages and disadvantages of not specifying Numeric precision

user_8479974 0
Viewed 735

In PostgreSQL 10, I am creating a table that contains a Numeric column. I know the values in this column must be precise, but I'm not sure what the exact size of the final values will be.

value NUMERIC

vs.

value NUMERIC(20, 2)

What would be the advantages or disadvantages of not specifying the precision as opposed to giving it an arbitrarily large precision?

database-design postgresql sql
2 Answers

Specifying precision can have two effects on your data:

  1. Rounds the value:

    SELECT 100.005::NUMERIC(20,2) -- 100.01

  2. Raises an error if your value is too large:

    SELECT 100.005::NUMERIC(3,2) -- ERROR: numeric field overflow

In terms of best practice - I think it depends on the nature of your data. Often times explicit is better than implicit, though.

user_59216930

The disk size required by a numeric is restricted by its scale. If no scale is specified, the limit is the greatest possible scale, i.e. 16383

The size is computed as follow :

Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. [...] The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

So if your input values have a fixed format, using arbitrarily large precision or no scale at all will have no difference. If however you get your value with an "infinite" number of decimals or meaningless decimals, using no scale will require more disk space.

user_76355690
Related