Re: Failure to coerce unknown type to specific type

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kevin Grittner <kgrittn@ymail.com> writes:
>> I recall two constructs that we had in production that caused some
>> pain moving to PostgreSQL.
>
>> Here's one:
>
>> test=# insert into x values (coalesce(null, null));
>> ERROR:  column "d" is of type date but expression is of type text
>
> I don't have a lot of sympathy for that one.  coalesce(null, null)
> isn't legal at all per SQL spec

I don't get that from my reading of the SQL spec.  A COALESCE
clause is (and always has been) considered a short form of the CASE
clause (not to be mistaken for a function, for example).  The spec
section 6.11 1) c) very explicitly requires
  COALESCE(NULL, NULL)
be the exact equivalent of
  CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END

Yet in PostgreSQL the long form of the CASE clause returns the same
thing as a bare NULL, while the short form (COALESCE) gives an
error.  Please indicate what in the spec makes you think that
COALESCE(NULL, NULL) should ever be treated differently from a bare
NULL, because I've looked at the spec and I'm not seeing anything
to support what you said.

> Otherwise the result type of coalesce() isn't well-defined, and there is
> nothing at all in the spec that would suggest looking to surrounding
> context to decide that.

The definition of COALESCE says that when there are different types
the result type should be determined according to section 9.3
(Result of data type combinations).  Because the organization of
our code doesn't lend itself well to conforming to the standard in
that regard, I realize that we are dealing in practical
compromises; but let's not pretend the spec is not clear about
this.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company