PostgreSQL: SQLstate: 22003 数字溢出

Question

我在 PostgreSQL (mixbest) 中有一个包含 73018 行的表。我要选择的字段是:

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

我正在尝试 SELECT，但出现以下错误 SQLstate:22003 数字溢出。我的选择:

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

我知道问题出在 EXP() 上，因为我已经用表达式 (SUM(LN(1-pciv)))) 尝试了相同的选择，但我没有得到同样的错误。

我试着只在几行中执行选择，它起作用了:

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;

你有什么建议吗？

Answer 1

我猜是这样的:

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;