Postgresql numeric and decimal is automatically rounding off

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

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



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

-
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)

StackExchange Database Administrators Q#242837, answer score: 8