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.
No argument that it would be nice to have a more apropos error message.However, that's harder to achieve than you
realize.
Here's a simplified version what happens:
1. you hand PostgreSQL an unadorned NULL. It realizes it doesn't have a
type, and makes it temporarily the default type (text) in hopes that the
next stage will provide a 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.
3. you try to assign the result of MIN() to a column of type "double".
This is when the error is encountered. The planner/executor doesn't
know that the reason min() is emitting text is because you handed it an
unadorned NULL; it just knows that it was expecting a double, and it got
text. At this point, it can't tell the difference between min(NULL) and
min('Josh'::TEXT).
To get a better error message, the query engine would need to reach back
to step (1) when it encounters the error at step (3).
The alternative would be to disallow unadorned NULLs entirely, which
would break thousands of applications.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com