>>> 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