Writing 0.001 to Postgres 9.x via data adapter results in a weird 0.00 value

Question

Writing 0.001 to Postgres (9.1 to 9.3) via a .net data adapter results in a weird 0.00 value (in pgAdmin) that appears as zero but is not.

In fact, simple queries like SELECT 1/(SELECT "weird_field" ...) FROM ... correcly gives 1000.

More complicated queries (with division) surprisingly result in a division by zero error.

Also, ordering by in Navicat correctly shows those value between 0.0011 and 0.0009

We use Devart libraries to connect to the database, but the culprit seems to be the data adapter (or at least the combo of these two) because a simple direct query, still via Devart drivers, doesn't produce the same result.

Any idea on what's going on?

--

EDIT:

Type on DB is numeric, in the program is represented as double decimal.

pSQL prints this:

--
0.00
(1 row)

--

EDIT 2:

log_statement = 'all' gives a weirdER result:

UPDATE "TABLE" SET ... "WEIRD_FIELD"=$8 ... WHERE ...

DETAIL:  parameters: $1 = '7', $2 = '7', $3 = '18', $4 = '18', $5 = 'V03', $6 = 'Hz',
                     $7 = 'Hz', $8 = '0.00', $9 = '0', $10 = '2', $11 = '0'

The parameter for the weird field is printed as zero (0.00), but clearly it is not...

Note that the value in the DataGridView populated by DataAdapter shows up the correct 0.001.

--

EDIT 3 (Maurice):

The issue with the Devart adapter seems to be fixed. Using the latest version, I no longer see the problem. I think it is related to this specific fix: 7.3.293 20-Nov-14: The bug with precision loss when working with PgSqlType.Numeric via the protocol 3 is fixed I upgraded my software using the latest Devart assemblies and now everything works as expected.

Answer 1

We found out that the problem is in the way PgSql represents numerics (decimal) with unspecified precision and scale.

The value in the DB seems to be correct (0.001) but in some operations it is truncated:

"weird_field" + 0.001
---------------------
                0.002

but

"weird_field" * 2
--------------------
                0.00

and

"weird_field" * 5
--------------------
                0.01

This also explains why some queries gives a division by zero error.

The solution is to specify precision and scale, for example we chose numeric(38,28) (that we also use for decimal in Oracle) and all works fine.

--

Looking at PgSql documentation, we didn't find nothing about this behavior, so we believe it's a bug:

Specifying:
NUMERIC
without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale.

Strange thing is also that 0.000001 (et similia) doesn't get truncated, whilst 0.001 does.