Re: COALESCE requires NULL from scalar subquery has a type

David G. Johnston wrote on 08/02/2016 16:05:
> On Mon, Feb 8, 2016 at 8:25 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj
> <mailto:pgsqladmin(at)geoff(dot)dj>>wrote:
>
> On 8 February 2016 at 14:49, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
> > Yup. The output column type of the sub-SELECT is determined without
> > reference to its context, so there's nothing causing the
> unknown-type
> > literal to get assigned a definite type.
>
> Mm. I can follow that, although it makes me unhappy that casting the
> literal to a known type fixes this, it seems unintuitive.
>
>
> ​While explicit casting of literals can at times be annoying and
> seemingly unncessary I wouldn't call it unintuitive.

I think if I was designing Postgres's type system (or SQL itself?) from
scratch, I'd try to make literals look less like strings. I think part
of what's unintuitive is that we're so used to thinking of 'Yes' as
representing a text value, when Postgres doesn't see it that way.
Perhaps if it was "Select text<yes>", and even "Select int<42>" it would
be more obvious that "Select <yes>" or "Select <42>" required type
inference.

But that's just dreaming...

Regards,
--
Rowan Collins
[IMSoP]