UPDATE tbl SET score = divide_double_default(score, s, 1e-200) ... UPDATE tbl SET score = multiply_double_default(score, s, 999999999) ... Code the divide_double_default/multiply_double_default functions with error handling that will return the desired value (either zero or the supplied parameter) if an exception is thrown; probably with a WARNING/NOTICE raised as well. David J. > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Shane W > Sent: Monday, May 23, 2011 4:08 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Trapping errors > > Hello list, > > I have a table with double precision columns and update queries which > multiply and divide these values. I am wondering if it's possible to catch > overflow and underflow errors to set the column to 0 in the case of an > underflow and a large value in the case of an overflow. > > Currently, I have an exception handler in a PLPGSQL ufunction that sort of > does this. > > begin > update tbl set score = score/s > exception when numeric_value_out_of range then update tbl set score=0 > where cast(score/s as numeric) < 1e-200 end; > > But this is messy since the exception needs to rescan the entire table if even > one row fails the update. Is there a better way to do this? > > Best, > Shane > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
