Re: weird error message

Not that it matters but the result set for this is a single row single
column with a value of 'NULL for Oracle.
SELECT case WHEN COALESCE(dt, i) IS NULL THEN 'NULL' else 'NOTNULL' END
rslt
FROM (SELECT null AS dt, null as i FROM dual ) q;
Probably, making an easy conversion path from Oracle to Postgres is not
high on your list of considerations but right now it is high on mine.

On Fri, May 6, 2016 at 10:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Fri, May 6, 2016 at 9:35 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> I think there's a rough consensus that it'd be okay to resolve unknown
> to
> >> text at the time that the subquery is parsed, if there's no reason to
> >> resolve it to something else.
>
> > ​I'm not sure that buys us a lot here...
>
> > SELECT case when dt IS NOT NULL then dt else i end FROM (SELECT null AS
> dt, 1 AS i) q;
> > failed to find conversion function from unknown to integer
>
> > SELECT case when dt IS NOT NULL then dt else i end FROM (SELECT
> '2'::text AS dt, 1 AS i) q;
> > ​SQL Error: ERROR: CASE types integer and text cannot be matched
>
> Well, you're right that that type of situation isn't going to "just work";
> the user is going to have to cast the null explicitly, because I do not
> think it's reasonable to expect the system to guess that resolving the
> null as integer is what's needed. The point is to give a less opaque
> error message, and I think the latter error message is much better than
> what you get now. Also, defaulting to text is what happens in some
> related cases, notably
>
> SELECT case when dt IS NOT NULL then dt else i end FROM (SELECT DISTINCT
> null AS dt, 1 AS i) q;
> ERROR: CASE types integer and text cannot be matched
>
> In this case we resolved the unknown as text so that the DISTINCT could
> have some well-defined behavior. An ORDER BY targeting that column would
> do the same. So IMV it's already surprising that we don't resolve the
> unknown as text without those things.
>
> > I'm not certain why we wouldn't just add implicit casts from unknown to
> > other types.
>
> Because that would involve *far* more widespread, and less principled,
> changes in behavior. Implicit casts affect every part of the language,
> whereas the actual problem here is restricted to "what's the type of
> this sub-select output column?".
>
> > I'm willing to go read where this option has been discussed and
> dismissed -
> > just point me in the right direction please.
>
> It's come up repeatedly, though I do not have time right now to search
> the archives.
>
> regards, tom lane
>