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

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.

thanks everyone,

Bene

On Fri, Jul 12, 2013 at 3:17 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> Benedikt Grundmann wrote
> > A third party application we use generates SQL queries. Here is query it
> > generated that broke today and for which I have a hard time arguing that
> > the postgres behavior is correct (minimally the error message is
> > confusing):
> >
> > =# create temporary table foo (b double precision );
> > CREATE TABLE
> > Time: 40.368 ms
> > =# insert into foo select min(NULL);
> > ERROR: column "b" is of type double precision but expression is of type
> > text
> > LINE 1: insert into foo select min(NULL);
> > ^
> > HINT: You will need to rewrite or cast the expression.
> >
> > So why does min(NULL) have type text? According to the docs it has the
> > type of the input. The value is itself NULL which is a valid member of
> > all
> > types in SQL isn't it?
> >
> > So what is going on?
> >
> > Thanks,
> >
> > Bene
>
> Ideally PostgreSQL would be smart enough to recognize that "min(NULL)" is
> of
> an unknown type and thus would use the definition of "foo" to coerce NULL
> to
> the desired type. I cannot explain why it does not do this but from the
> example it cannot.
>
> Using a literal NULL without an explicit type-cast is not recommended as
> the
> system cannot always accurately figure out what type you mean for it to
> use.
> Being a valid value for all types does not mean it magically switches to
> fit
> whatever usage is required. Columns are typed, not values per-se, and so
> NULL can belong in any column but once it is part of that column it takes
> on
> that column's type.
>
> The query you show is pretty pointless since the intent of "min" is to take
> a column over which to aggregate; not a literal which will only ever return
> itself.
>
> In short the SELECT query is trying its best to execute and so in the
> presence of an unadorned NULL - and being unable to infer the type from
> context - it simply uses the default type which is "text". The SELECT
> executes just fine, and outputs a "min" column of type "text" which when
> supplied to the table "foo" causes the type mis-match for column "b" on
> "foo".
>
> The PostgreSQL behavior is "simple" because it does not infer the type of
> NULL from the column in foo but it is not required to do so its failure is
> not wrong. The error message, given what does occur, makes perfect sense
> and is easy enough to trace (i.e., what column is feeding foo.b from the
> SELECT statement; then, why is that column being seen as "text").
>
> PostgreSQL is in the opinion of some too verbose in its requirement to be
> explicit regarding types but it does make for less buggy code overall.
> This
> particular use-case may be solvable but I'd argue that your example is not
> likely to convince anyone that it is a serious enough problem worth the
> effort it would take to do so.
>
> 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-tp5763586p5763587.html
> Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>