Why are my PostgreSQL "real" data being rounded?

Question

I have a table with a column named total having the real data type.

But I'm finding that this column is being rounded when I query its value, as illustrated in the following:

SELECT total FROM data WHERE id='xe54g';
 total 
---------
  105861

UPDATE data SET total=105860.5 WHERE id='xe54g';        
UPDATE 1

SELECT total FROM data WHERE id='xe54g';
 total 
---------
  105860

UPDATE data SET total=105860.7 WHERE id='xe54g';        
UPDATE 1

SELECT total FROM data WHERE id='xe54g';
 total 
---------
  105861

\d+ data
   Column  |  Type | Collation | Nullable |  Default  | Storage  | Stats target | Description 
-----------+-------+-----------+----------+-----------+----------+--------------+-------------
 total     | real  |           |          | 0         | plain    |              |

How do I get it to return the full precision? I'm using PG 11.1.

Possible duplicate of [How to round REAL type to NUMERIC?](https://stackoverflow.com/questions/21576685/how-to-round-real-type-to-numeric) — A. J. Parr, May 28 '19 at 10:31

score 1 · Accepted Answer · answered May 28 '19 at 10:38

1

You can get the full precision by setting

extra_float_digits = 3

Then PostgreSQL will also display decimal digits that may contain rounding errors.

Note that real has a low number of significant digits. You'd be happier if you used double precision.

answered May 28 '19 at 10:38

Laurenz Albe

209,280
17
206
263

Aah, I see that `real` has a 6 decimal digits precision. The numbers I'm using are already 6 digits without the decimal places, and hence the issue. It works OK for e.g. `SET total=90586.7`. Can I safely convert my `real` column to `double precision`? – drmrbrewer May 28 '19 at 10:41
Yes, but it may take a while for a big table, because the table has to be rewritten. `double precision`takes 8 bytes, while `real`only uses 4. – Laurenz Albe May 28 '19 at 10:43
OK thanks. Is this the correct way to convert: `ALTER TABLE data ALTER COLUMN total TYPE double precision;` ? – drmrbrewer May 28 '19 at 10:46
why not using `decimal` datatype instead? as it is a exact datatype.. `double precision` is still inexact or approximate, @drmrbrewer i would advice using `decimal` datatype especially when you are dealing with money or other totals it has to be exact – Raymond Nijland May 28 '19 at 11:42
@RaymondNijland thanks, will consider that. Again, assume it's safe to convert the column data type from `real` or `double precision` to `decimal` with e.g. `ALTER TABLE data ALTER COLUMN total TYPE decimal;` ? – drmrbrewer May 28 '19 at 12:34
almost correct @drmrbrewer but decimals need to defined like `decimal(4, 2)` for example it depends what you need, the 4 are the number before the comma and 2 are the number after so `decimal(4, 2)` stores the max off `9999.99` – Raymond Nijland May 28 '19 at 12:42
@RaymondNijland I thought the docs say that you can just specify `decimal` (or `numeric`) without limit? Whilst I'm happy to limit `scale` (decimal places) to `1` I don't want to limit overall `precision` (significant figures). See https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL : "without any precision or scale creates a column in which numeric values of any precision and scale can be stored" – drmrbrewer May 28 '19 at 12:50
2

Yes, you can use `decimal` (the same as `numeric`) without a type modifier. Use that data type if you need precision and `double precision` if you need speed. – Laurenz Albe May 28 '19 at 12:59
1

yes sorry for the confusion @drmrbrewer not sure why i was thinking and making the comment that `decimal(4, 2)` was required for decimal blame it to the leak of caffine or i might be reading to much ANSI/ISO standards documents about SQL where they are required.for decimal datatype – Raymond Nijland May 28 '19 at 13:06

Why are my PostgreSQL "real" data being rounded?

1 Answers1