On Fri, Jul 12, 2013 at 8:47 AM, Benedikt Grundmann
<bgrundmann@janestreet.com> 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?
This is not a question for -hackers.
Postgres is strictly typed -- there is no variant type. So even
though some functions can configured to support multiple input types
via overloading, 'any' arguments, etc. when stuff actually gets done
type coercion has to take place and text is chosen as a type of last
resort.
merlin