The Select clause returns the same value for different columns

advertisements

I'm using native request to select data from two tables.
When I execute my request on pgAdmin console, the result returned well, but when I execute request from my application, same value returned for all columns when I use round() function. below the request

SELECT s.id,
       p.nom,
       p.prenom,
       round(s.column1, 2),
       round(s.column2, 2),
       round(s.column3,2)
FROM table1 s
   INNER JOIN table2 p USING (id)
where s.id_type = 2
and s.id_example=7
ORDER BY p.nom, p.prenom

When I execute this request, If round(s.column1, 2) returns 20 as value, I have the same value for round(s.column2, 2) and round(s.column3,2).
I tried to use aliases but I received the following exception:

Caused by: org.postgresql.util.PSQLException: Mauvaise valeur pour le type BigDecimal : NaN


It's difficult to tell without you showing more code…

The error is basically complaining that NaN (aka not a number) is not a valid value for the BigDecimal type, which I understand as Java's equivalent to Postgres' numeric type.

Postgres accepts NaN for the float and arbitrary precision types, but not for integer types:

denis=# select 'NaN'::float;
 float8
--------
    NaN
(1 row)

denis=# select 'NaN'::numeric;
 numeric
---------
     NaN
(1 row)

denis=# select 'NaN'::bigint;
ERROR:  invalid input syntax for integer: "NaN"
LINE 1: select 'NaN'::bigint;
               ^

As such, it's probably an indication that your ID columns are not integers, that you've incorrectly configured your ORM in such a way that columns are mapped to incorrect equivalent Java types, or that you've some values in your table (NaN) that Java doesn't support and that — I'm guessing… — should in fact be NULL.