Re: coalesce with all nulls can only be assigned to

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: coalesce with all nulls can only be assigned to
Date: 2006-11-29 18:41:09
Message-ID: 456D7FE5.EE98.0025.0@wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

>>> On Wed, Nov 29, 2006 at 12:15 PM, in message
<21074(dot)1164824140(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tony Caduto 2006-11-29 19:00:03 Re: Development of cross-platform GUI for Open Source DBs
Previous Message Vivek Khera 2006-11-29 18:24:25 Re: Postgresql data integrity during RAID10 drive rebuild