PostgreSQL Numeric and Decimal Auto-Rounding Issue – Solutions

datatypespostgresqlpostgresql-11

CREATE TABLE IF NOT EXISTS ttable (
    tcol decimal(9,7)
);

insert into ttable(tcol) values(17.4604786);

the value is getting stored as 17.46

Happens the same if I use decimal/numeric type

I am using

PostgreSQL 11.2 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit.

Tool
SQL Workbench/J Build 124 (2018-08-20 22:43)

Java version: 1.8.0_211 (64 bit).

Connection info:
Product Name: PostgreSQL
Product Version: 11.2
Product Info: 11.2
Driver Name: PostgreSQL JDBC Driver
Driver Class: org.postgresql.Driver
Driver Version: 42.2.6
Isolation Level: READ COMMITTED
Workbench DBID: postgresql

Best Answer

It seems to be the default behaviour of SQL Workbench, have a look at this article:

How do I change the resolution or scale of decimal data type on SQL Workbench.

Quoted from the article:

Normally, SQL Workbench doesn't display the decimal data with the full scale. By default the scale is 2. We can change the scale by the setting.

Solution

  1. Open Data formatting settings. SQLWorkBench -> Preferences ->Data formatting

  2. Change Decimal digits The default is 2. In this case, it should be at least 11.

And according to SQL Workbench manual about Data formating:

Decimal digits

Define the maximum number of digits which will be displayed for numeric columns. This only affects the display of the number, not the storage or retrieval. Internally they are still stored as the DBMS returned them. To see the internal value, leave the mouse cursor over the cell. The tool tip which is displayed will contain the number as it was returned by the JDBC driver. When exporting data or copying it to the clipboard, the real value will be used.

If this value is set to 0 (zero) values will be display with as many digits as available.

(Bold is mine)

Related Question