Query with NVL function results in "ERROR: function nvl(bigint, integer) does not exist"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

The NVL function reports "ERROR: function nvl(bigint, integer) does not exist".
 

For example:

testdb=# select nvl(sum(0), 0);
ERROR: function nvl(bigint, integer) does not exist
LINE 1: select nvl(sum(0), 0);
^

Hint: If no function matches the given name and argument types, it may be required to add explicit type casts.

Environment

Cause

The function "nvl" accepts equivalence of types for both parameters as described in the definition of the function.

testdb=# \df+ oracompat.nvl
                                                                 List of functions
  Schema   | Name | Result data type |  Argument data types   |  Type  | Data access | Volatility |  Owner  | Language | Source code | Description 
-----------+------+------------------+------------------------+--------+-------------+------------+---------+----------+-------------+-------------
 oracompat | nvl  | anyelement       | anyelement, anyelement | normal | no sql      | immutable  | ####### | c        | ora_nvl     | 
(1 row)

In order for this to work, substitute the expected value with a value for the same type. For example, in the above error, 0 (zero) is returning as int datatype and sum(0) is a bigint datatype.

Resolution

Cast the type with the result as explained below:

testdb=# select oracompat.nvl(sum(0), 0::bigint);
 nvl 
-----
   0
(1 row)

An alternate approach involves users defining their own NVL function (wraparound) to support bigint and smallint as the input/output parameter.

testdb=# CREATE OR REPLACE FUNCTION NVL(bigint, anyelement) returns bigint
AS 'select coalesce($1, $2::bigint)'
LANGUAGE SQL
IMMUTABLE;
testdb=# select oracompat.nvl(sum(0), 0);
 nvl 
-----
   0
(1 row)