Re: COALESCE requires NULL from scalar subquery has a type - Mailing list pgsql-general

On Mon, Feb 8, 2016 at 8:25 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

On 8 February 2016 at 14:49, Tom Lane <tgl@sss.pgh.pa.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]