4
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

  • 1
    How are you getting the value? Which client tool? – McNets Jul 14 at 17:26
  • @McNets Updated my question with info on tools – Neo Jul 14 at 17:38
7

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)

  • you can always double check easily using one of the fiddles - sqlfiddle.com/#!15/bb76b/1 – SQLRaptor Jul 14 at 17:48
  • @McNets thank you. – Neo Jul 14 at 17:51
  • @SQLRaptor thank you for introducing me to sqlfiddle – Neo Jul 14 at 17:52
  • I'm glad to help, there are more fiddles like dbfiddle.uk and rextester – McNets Jul 14 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.