Обсуждение: PostgreSQL FUNCTION return problem
Hello, i have the following problem and i was wondering if you coulde help me. I'll try to describe it as in depth as i can.
I have a FUNCTION in a postgresql database which subtracts two DOUBLE PRECISION FIELDS and returns the result. The problem is that when the result should be zero, the number i
get is a number ridiculously close to zero in scientific notation (for example 2.4697823124E-14) but not zero. I know (or at least i think i know) the problem lies within the way postgresql and Java communicate because if a connect through a console to the db and run the function i get the result right (zero) but if i debug the java code i get ther wrong non-zero-but-very-close result
this is the function:
CREATE OR REPLACE FUNCTION getSaldoParticipante(BIGINT) RETURNS DOUBLE PRECISION AS '
DECLARE
idParticipante ALIAS FOR $1;
result DOUBLE PRECISION;
BEGIN
result := 0;
Select (Select CASE WHEN saldo ISNULL then 0 else saldo end FROM (Select sum(PG.saldo) as saldo FROM pagos.pagos PG WHERE PG.saldo > 0 AND PG.participante=idParticipante AND PG.deleted = false) tmp)-(Select CASE WHEN saldo ISNULL then 0 else saldo end FROM (Select sum(DE.saldo) as saldo FROM pagos.deudas DE WHERE DE.deleted = false AND DE.participante=idParticipante) tmp) INTO result;
RETURN result;
END;'
LANGUAGE 'plpgsql' CALLED ON NULL INPUT;
this is the Java code where i get the result that should be zero but instead is just very close to zero
String query = "select getSaldoParticipante(?)";
params.add(new ParameterValue(idParticipante, Types.BIGINT));
try {
conn = tx.getConnection();
//preparo y ejecuto el statement
pstmt = prepareStatement (conn, query, params);
rs = pstmt.executeQuery();
//itero los registros
while (rs.next()) {
//the problems appears at the following line
result = rs.getDouble("getSaldoParticipante");
}
}
catch(Exception e){
...
the only thing that works here is to use rs.getInt instead of rs.getDouble, but that is not good enough since i dont want to truncate the decimal part of the number.
So, a lot of thanks in advance, any help will be greatly appreciated.
¡Saludos!
Rafael
I have a FUNCTION in a postgresql database which subtracts two DOUBLE PRECISION FIELDS and returns the result. The problem is that when the result should be zero, the number i
get is a number ridiculously close to zero in scientific notation (for example 2.4697823124E-14) but not zero. I know (or at least i think i know) the problem lies within the way postgresql and Java communicate because if a connect through a console to the db and run the function i get the result right (zero) but if i debug the java code i get ther wrong non-zero-but-very-close result
this is the function:
CREATE OR REPLACE FUNCTION getSaldoParticipante(BIGINT) RETURNS DOUBLE PRECISION AS '
DECLARE
idParticipante ALIAS FOR $1;
result DOUBLE PRECISION;
BEGIN
result := 0;
Select (Select CASE WHEN saldo ISNULL then 0 else saldo end FROM (Select sum(PG.saldo) as saldo FROM pagos.pagos PG WHERE PG.saldo > 0 AND PG.participante=idParticipante AND PG.deleted = false) tmp)-(Select CASE WHEN saldo ISNULL then 0 else saldo end FROM (Select sum(DE.saldo) as saldo FROM pagos.deudas DE WHERE DE.deleted = false AND DE.participante=idParticipante) tmp) INTO result;
RETURN result;
END;'
LANGUAGE 'plpgsql' CALLED ON NULL INPUT;
this is the Java code where i get the result that should be zero but instead is just very close to zero
String query = "select getSaldoParticipante(?)";
params.add(new ParameterValue(idParticipante, Types.BIGINT));
try {
conn = tx.getConnection();
//preparo y ejecuto el statement
pstmt = prepareStatement (conn, query, params);
rs = pstmt.executeQuery();
//itero los registros
while (rs.next()) {
//the problems appears at the following line
result = rs.getDouble("getSaldoParticipante");
}
}
catch(Exception e){
...
the only thing that works here is to use rs.getInt instead of rs.getDouble, but that is not good enough since i dont want to truncate the decimal part of the number.
So, a lot of thanks in advance, any help will be greatly appreciated.
¡Saludos!
Rafael
Rafael Barrera Oro wrote: > Hello, i have the following problem and i was wondering if you coulde help > me. I'll try to describe it as in depth as i can. > > I have a FUNCTION in a postgresql database which subtracts two DOUBLE > PRECISION FIELDS and returns the result. The problem is that when the result > should be zero, the number i > get is a number ridiculously close to zero in scientific notation (for > example 2.4697823124E-14) but not zero. Start here: http://www.google.com/search?q=floating+point+rounding The short answer is - never expect the subtraction of two "equal" floating point values to be zero. Instead of testing with equality, test with a very small range. If you really need exact values (say, you're working with money) use the NUMERIC type instead. -- Craig Ringer