PostgreSQL: SQLstate: 22003 numeric overflow

Question

I have a table in PostgreSQL (mixbest) with 73018 rows. The fields that I want to select are:

sample integer
m      integer,
pciv   double precision,
aggrec double precision 
soil   character(1)

I'm trying a SELECT but I get the following error SQLstate: 22003 numeric overflow. My select:

SELECT sample, m, 1-(EXP(SUM(LN(1-pciv)))) pciv, avg (aggrec) aggrec_avg, soil 
FROM mixbest  
GROUP BY sample,  m,  soil;

I know the problem is the EXP() due to I've tried the same select with the expression (SUM(LN(1-pciv)))) and I don't get the same error.

I tried to execute the select only in a few lines, and it works:

SELECT sample, m, 1-(EXP(SUM(LN(1-pciv)))) pciv, avg (aggrec) aggrec_avg, soil 
FROM mixbest 
WHERE  sample< 4492 GROUP BY sample,  m,  soil;

Do you have any suggestion?

Could you show what select max(pciv) from (SELECT sample, m, SUM(LN(1-pciv)) pciv, avg (aggrec) aggrec_avg, soil FROM mixbest GROUP BY sample, m, soil) t; gives? — stas.yaranov
– stas.yaranov, Commented Nov 12, 2015 at 17:47
Sure, the result of select max(pciv) from (SELECT sample, m, SUM(LN(1-pciv)) pciv, avg (aggrec) aggrec_avg, soil FROM mixbest GROUP BY sample, m, soil) t; is -16.1561356371564 — Laia Linares Ortells
– Laia Linares Ortells, Commented Nov 13, 2015 at 7:59
Ok, negative numbers, now could you show the result of select min(pciv) from (SELECT sample, m, SUM(LN(1-pciv)) pciv, avg (aggrec) aggrec_avg, soil FROM mixbest GROUP BY sample, m, soil) t; ? — stas.yaranov
– stas.yaranov, Commented Nov 13, 2015 at 8:08
Definitely, it's too small. I mean, Postgresql have to solve 1/(e^8075). e^8075 is extremely large value. — stas.yaranov
– stas.yaranov, Commented Nov 13, 2015 at 8:23

stas.yaranov · Accepted Answer · 2015-11-13 09:21:12Z

1

Something like this, I guess:

create or replace function mixbest_avg(out sample int, out m int, out pciv double precision, out aggrec_avg double precision, out soil character(1))
    returns setof record as
$$
declare
    rec record;
begin
    for rec in
        SELECT t.sample _sample, t.m _m, SUM(LN(1-t.pciv)) _pciv, avg(t.aggrec) _aggrec, t.soil _soil
            FROM mixbest t
            GROUP BY t.sample, t.m, t.soil
    loop
        begin
            rec._pciv = 1 - exp(rec._pciv);
        exception
            when numeric_value_out_of_range then -- here we catch an exception
                rec._pciv = 0; -- or other default value
        end;

        select rec._sample, rec._m, rec._pciv, rec._aggrec, rec._soil into sample, m, pciv, aggrec_avg, soil;
        return next;
    end loop;
end
$$ language plpgsql;

answered Nov 13, 2015 at 9:21

stas.yaranov

1,78711 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: SQLstate: 22003 numeric overflow

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related