Kevin Grittner wrote:
Tom Lane <tgl@xxxxxxxxxxxxx> 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
