0

I am trying to migrate an oracle DB application to postgres. I can't seem to find an equivalent of NANVL in postgres. Is there a way to emulate the same ?

From the Oracle manual:

The NANVL function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. It instructs Oracle Database to return an alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2.

Whimsical
  • 5,985
  • 1
  • 31
  • 39
  • It (posgresql) doesn't have such function. What you can do is a mix of `coalesce` with one of the functions here: https://stackoverflow.com/questions/25858859/how-to-check-if-number-is-nan – Jorge Campos Jun 21 '17 at 01:04
  • 1
    Since you're probably looking for answers from Postgres experts, you may want to explain what NANVL does in Oracle to spare them having to read Oracle documentation. – mustaccio Jun 21 '17 at 01:22

2 Answers2

4

In Postgres you can compare a numeric value to the special constant 'NaN':

select some_column = 'NaN'
from some_table;

So you can write a function that will implement nanvl():

create function nanvl(p_to_test numeric, p_default numeric)
  returns numeric
as
$$
   select case 
            when p_to_test = 'NaN' then p_default 
            else p_to_test 
          end;
$$
language sql;
1

Edit

Of course, a_horse_with_no_name is correct.

Everything is simpler: just a CASE with check on equality to NaN is needed http://sqlfiddle.com/#!17/37528

Everything below is incorrect.

Similar to Jorge Campos advice: rip-off isnumeric from isnumeric() with PostgreSQL and modify it to:

/* Ripoff https://stackoverflow.com/questions/16195986/isnumeric-with-postgresql */
CREATE OR REPLACE FUNCTION nanvl(text, NUMERIC) RETURNS NUMERIC AS $$
DECLARE x NUMERIC;
BEGIN
    x = $1::NUMERIC;
    RETURN x;
EXCEPTION WHEN others THEN
    RETURN $2;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;
GO

SQLFiddle: http://sqlfiddle.com/#!17/aee9d/1

Alex Yu
  • 3,412
  • 1
  • 25
  • 38
  • According to [the manual](https://docs.oracle.com/database/121/SQLRF/functions115.htm#SQLRF06311) the function only checks for `NaN` not if a character value is a valid value. `nanvl('foo', 1)` will generate an error. –  Jun 21 '17 at 06:02