Re: coalesce with all nulls can only be assigned to

Kevin Grittner wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> We never do assume that a text literal is a valid date. I won't bore
> you with all the details unless you ask for them, but we're running on
> Java and generating literals based on the object type passed to a low
> level method. A null has no type to use as the basis of a cast.

Unfortunate. Does your method know what type the database column is?

>> If you cast at least one of the nulls to DATE, you'll get what you
> want.
>
> I realize that, and I'm working on modifying our framework to get type
> information down to where we can do that for nulls. The problem is,
> this is a big enough change to potentially cause problems and hold up
> the migration to PostgreSQL on the majority of our databases for an
> application release cycle (three months), so I'm hoping for a less
> drastic workaround. It seems odd that a bare null works, but a coalesce
> of two nulls fails.

It's the coalesce that has the problem, not the insert. The coalesce is
deciding that it's working on text, and so returns text.

> It also seems odd that the automatic casting from
> text to date fails to cover this. (I tried creating a cast to cover
> this and it told me there already was one.)

There is a cast from text to date, but I don't think it's automatic...
(checks pg_cast) - no, it's marked as explicit. You could try marking
the cast as implicit, but I'd be concerned about unexpected casts occurring.

Another option I can think of: Spot the case where all values in the
coalesce are null and just replace with a single literal null.

--
Richard Huxton
Archonet Ltd