Re: column "b" is of type X but expression is of type text

Josh Berkus wrote
> On 07/12/2013 07:28 AM, Benedikt Grundmann wrote:
>> Thanks David,
>>
>> I like the fact that postgres is explicit in it's types. All I'm arguing
>> is that error message is misleading. And that I had a hard time
>> understanding why happened what happened. The part I was missing is that
>> despite supporting an any type the necessary type inference is very very
>> local and quickly resorts to the default type.
>
> 2. you call min(). Min() works for many datatypes. Min() says: "can I
> work for text?" The answer is "yes", so at this point the NULL which
> was "default text" becomes *really* text.
>
> .
> .
> .
>
> The alternative would be to disallow unadorned NULLs entirely, which
> would break thousands of applications.

In the absence of the function call the system is able to delay resolving
the type until later in the query:

SELECT *
FROM (VALUES ('2013-02-01'::date), ('2013-01-01'), (NULL) ) vals (col1);
--works

SELECT *
FROM (VALUES ('2013-02-01'::date), ('2013-01-01'), (min(NULL)) ) vals
(col1); --fails

I have no idea how this mechanism works but ISTM that the planner could, for
"anyelement", look at where the result of the function call is used and add
a cast to the function input value to match the desired result type if the
input type is "undefined".

I'm curious what you would consider to be a "more apropos error message" in
this situation; regardless of how difficult it would be to implement.

I am also curious if you can think of a better example of where this
behavior is problematic. The query for this thread is not something that I
would deem to be good SQL.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/column-b-is-of-type-X-but-expression-is-of-type-text-tp5763586p5763615.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.