coalesce with all nulls can only be assigned to text

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-general(at)postgresql(dot)org>
Subject: coalesce with all nulls can only be assigned to text
Date: 2006-11-29 17:07:27
Message-ID: 456D69EF.EE98.0025.0@wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2006-11-29 17:11:23 Re: Postgresql data integrity during RAID10 drive rebuild
Previous Message Scott Marlowe 2006-11-29 17:06:19 Re: Postgresql data integrity during RAID10 drive rebuild