>>> On Wed, Nov 29, 2006 at 12:15 PM, in message
<[EMAIL PROTECTED]>,
Tom Lane <[EMAIL PROTECTED]> wrote:
> "Kevin Grittner" <[EMAIL PROTECTED]> 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.
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.
> 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 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.)
Thanks,
-Kevin
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
