Kevin Grittner wrote:
> Tom Lane <tgl@sss.pgh.pa.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
