Re: coalesce with all nulls can only be assigned to text - Mailing list pgsql-general

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> [ "coalesce(null, null)" yields type TEXT ]

Well, it has to yield *something*.  You'd get the same result from
"coalesce('2006-11-29', '2006-11-30')" ... you might think this looks
like dates, but it's just some untyped literals and the parser chooses
to resolve those as TEXT if there's no other clue anywhere in the
expression.

If you cast at least one of the nulls to DATE, you'll get what you want.

regression=# create table test_coalesce(f1 int not null, f2 date);
CREATE TABLE
regression=# 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.
regression=# insert into test_coalesce values (2, coalesce(null::date, null));
INSERT 0 1

            regards, tom lane