Re: coalesce with all nulls can only be assigned to

>>> On Wed, Nov 29, 2006 at 1:09 PM, in message
<456DDAFA(dot)3000803(at)archonet(dot)com>,
Richard Huxton <dev(at)archonet(dot)com> wrote:
> Kevin Grittner wrote:
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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?

No, it's a method that takes an object and generates a proper SQL
literal for the database product. (Portability is a big issue.)

>>> 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 seems like maybe it would be worth overloading the coalesce method
to handle this particular case differently. It might allow some queries
to optimize better. Maybe. On the other hand, it doesn't sound like it
comes up often, so it's likely not worth the effort.

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

Point taken. I would only do this as a temporary workaround -- it
doesn't seem like a good permanent solution. If I were to do this,
would I update the existing row in pg_cast or use the DROP CAST and ADD
CAST statements?

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

This would have to be done in the JDBC driver's handling of the "{fn
IFNULL" portability escape code. That might be a decent stop-gap. I
think I'll do that to support preliminary testing, and work on the
framework changes for the long-term solution.

Thanks,

-Kevin