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

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: column "b" is of type X but expression is of type text
Date: 2013-07-12 16:36:32
Message-ID: 51E03090.3000501@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2013-07-12 16:37:29 Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
Previous Message Peter Eisentraut 2013-07-12 15:11:39 Re: proposal: simple date constructor from numeric values