Here is sample code demonstrating the issue:
test=# create table test_coalesce(f1 int not null, f2 date);
CREATE TABLE
test=# insert into test_coalesce values (1, null);
INSERT 0 1
test=# insert into test_coalesce values (2, coalesce(null, null));
ERROR: column "f2" is of type date but expression is of type text
HINT: You will need to rewrite or cast the expression.
test=# create cast (text as date) with function date(text) as
assignment;
ERROR: cast from type text to type date already exists
test=# create cast (text as date) with function date(text) as
implicit;
ERROR: cast from type text to type date already exists
The last statement is not something which would make sense to hand
code, but we have a framework which is plugging in the arguments for the
coalesce function at run time. One solution to this is to modify the
framework to wrap any null with a cast to a type. Due to the effort and
risk of that approach, I'm looking for alternatives. Besides, the above
just doesn't make sense to me.
Any suggestions?
-Kevin