10

Recently I migrated a DB to PostgreSQL that has some columns defined as numeric(9,3) and numeric(9,4). In testing the app I have found that when data is saved to these columns there are trailing zeros being added to the value inserted. I am using Hibernate, and my logs show the correct values being built for the prepared statements.

An example of the data I am inserting is 0.75 in the numeric(9,3) column and the value stored is 0.750. Another example for the numeric(9,4) column: I insert the value 12 and the DB is holding 12.0000.

I found this related question: postgresql numeric type without trailing zeros. But it did not offer a solution other than to quote the 9.x documentation saying trailing zeros are not added. From that question, the answer quoted the docs (which I have also read) which said:

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.

However, like that question poster, I see trailing zeros being added. The raw insert generated by Hibernate in the logs does not show this extra baggage. So I am assuming it is a PostgreSQL thing I have not set correctly, I just can't find how I got it wrong.

Community
  • 1
  • 1
verbyk1924
  • 221
  • 1
  • 2
  • 8
  • What is the client where you see the zeros? Did you try psql? – Clodoaldo Neto Nov 13 '14 at 23:09
  • It's a web app and I am using Firefox as my browser. The App Server is in Java using Hibernate. The "modified" values with the trailing zeros are visible in the actual DB records (and come back that way to the client when queried). – verbyk1924 Nov 13 '14 at 23:26
  • What do you mean by _visible in the actual DB records_? What the client shows you? I already asked and you did not answer: Did you try psql? – Clodoaldo Neto Nov 13 '14 at 23:52
  • Yes, I did try a psql query with the same results. As the app I am supporting uses Java and Hibernate, that is the combination I need to make work. By "visible in the actual DB record", I am referring to what is in the table when viewed with a tool (in my case pgAdmin3). pgAdmin3 shows the trailing zeros as a result of the query it performs when you want to view/edit data...and that is what the web browser is showing coming from the server. – verbyk1924 Nov 14 '14 at 00:13

3 Answers3

11

I think this is it, if I am understanding "coerce" correctly in this context. This is from the PostgreSQL docs:

Both the maximum precision and the maximum scale of a numeric column can be configured. To declare a column of type numeric use the syntax:

NUMERIC(precision, scale)

The precision must be positive, the scale zero or positive. Alternatively:

NUMERIC(precision)

selects a scale of 0. Specifying:

NUMERIC

without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale.

Bold emphasis mine.

So it is misleading later in the same section:

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.

Bold emphasis mine again.

This may be true of the precision part, but since the scale is being coerced when it is defined, trailing zeros are being added to the input values to meet the scale definition (and I would assume truncated if too large).

I am using precision,scale definitions for constraint enforcement. It is during the DB insert that the trailing zeros are being added to the numeric scale, which seems to support the coercion and conflicts with the statement of no trailing zeros being added.

Correct or not, I had to handle the problem in code after the select is made. Lucky for me the impacted attributes are BigDecimal so stripping trailing zeros was easy (albeit not graceful). If someone out there has a better suggestion for not having PostgreSQL add trailing zeros to the numeric scale on insert, I am open to them.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
verbyk1924
  • 221
  • 1
  • 2
  • 8
7

If you specify a precision and scale, Pg pads to that precision and scale.

regress=> SELECT '0'::NUMERIC(8,4);
 numeric 
---------
  0.0000
(1 row)

There's no way to turn that off. It's still the same number, and the precision is defined by the type, not the value.

If you want to have the precision defined by the value you have to use unconstrained numeric:

regress=> SELECT '0'::NUMERIC, '0.0'::NUMERIC;
 numeric | numeric 
---------+---------
       0 |     0.0                                                                                                                                                             
(1 row)
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • new to PostgreSQL, so if I have a column defined in the DB as numeric(9,4) (like num_col following here) and I do a plain SQL insert (insert into blah (pk, num_col) values (1, 12);)...my 12 is now 12.0000 when I look at it using SQL or psql or pgAdmin3 with a select...(select num_col from blah;)? the scale and precision are defined on the column itself (as my limits), not in the SQL I am using during the select. during the insert the scale is padded by trailing zeros up to the defined limits. to me an insert of 12 should store 12 (not 12.0000) and a SQL select should get that same value back ? – verbyk1924 Nov 14 '14 at 12:12
2

You can strip training zeros with the trim_scale function from PostgreSQL v13 on. That will reduce the storage size of the number.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263