PostgreSQL NUMERIC displays as integer

[tonyb]

My goal is to link Calc to a PostreSQL database. So far most of what I need is working fine. However, for PostgreSQL NUMERIC data, it displays in Calc rounded up to the next integer value.

This is true in the Calc sheet itself where I have imported the data, also in the Calc data source pane where I can see the database table, and also in the intermediate Base file. In all places the numeric has been rounded and I lose the decimal places.

In PostgreSQL it is working fine. I use pgAdmin4 to admin the database and I can see the proper numeric data with decimal places intact.

LibreOffice Version 6.4.0.3 (x64)

Windows 10

PostgreSQL 12

localhost:5432 (standard connection)

[Ratslinger]

Hello,

There are some issues with a numeric field in Base using PostgreSQL. You do not state how the field was created or where. This determines the problem.

First there is this issue → PostgreSQL NUMERIC inconsistency. With this situation, if data is entered through pgAdmin4 it displays correctly in Base (and Calc). However if entered in Base, only whole numbers are accepted even when viewed in pgAdmin4.

SDBC connection - With this connection and defining the field in Base with decimals it works:

The image does reveal that the display must be set to show the correct number of decimals. Calc works fine.

JDBC connection (yours) - Defining the field in Base does not allow the user to set the number of decimal positions. This results in whole numbers only. Going to pgAdmin4 and modifying this field allow entry there but does nothing for Base - still whole number entry. If you create this field anew in pgAdmin with decimal positions, it then works in Base.

So yes there are bugs but can be worked around for the most part. Do not see any reported on this issue.

[Ratslinger]

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[tonyb]

@Ratslinger - Thank you for your reply. Yes, I did read the post you linked, which I interpreted as similar but slightly different. In that case, the author was able to read the correct decimal number in Base, but they could not write correctly from Base to PostgreSQL.

Also, you are right, I neglected to mention how I created the NUMERIC field in PostgreSQL. I did it the same as the author in the linked post, namely by leaving the precision and scale fields blank. Perhaps this suggests a common root cause.

Changing the data type to REAL in PostgreSQL did fix the issue. In order for the change to propagate through to Calc, I had to completely close and re-open Calc. Simply refreshing the data within Calc or Base did not work.

[tonyb]

Final note: the reason I chose NUMERIC to begin with is based on recommended best practice in the PostgreSQL documentation, which mentions that NUMERIC should be used for monetary calculations. I do not think this will be an issue for me since I plan to do all my calculations in Calc, but others may need to beware depending on their application and intended use cases.