Re: Old question - failed to find conversion function from - Mailing list pgsql-general

From Richard Huxton
Subject Re: Old question - failed to find conversion function from
Date
Msg-id 42DCDE07.4020906@archonet.com
Whole thread Raw
In response to Old question - failed to find conversion function from "unknown"  ("Ilja Golshtein" <ilejn@yandex.ru>)
Responses Re: Old question - failed to find conversion function from "unknown"
List pgsql-general
Ilja Golshtein wrote:
>
> When I invoke
>
> select 1 where 5 in (null)
>
> everything is Ok,
> though when I try
>
> select 1 where 5 in (select null)
>
> I get
>
> ERROR:  failed to find conversion function from "unknown" to integer.
>
> Is it desired behavior or subject to change
> in future versions of PG?

Well, it would obviously be better if PG could figure out it was safe,
but I'm not sure there's a general case where it is. You can see it's OK
because you know there's only one row in your SELECT result-set.
However, what if you had:
  ... (select null UNION ALL select '2005-01-01')
Now is this a set of "unknown" or "text" or "date"? Should PG change
it's decision based on what the rest of the query wants?

So - although this may change in future versions of PG, I wouldn't count
  on it never being a problem.

> I realize my simplified example looks radiculous while it came from reality.
> Sometimes my engine generates SQL queries does not know correct type
> and unable to cast, while PostgreSQL should have all information required.

How are you generating the SQL if you don't know the types involved? Are
you just blindly quoting all values? What do you do with arrays/points
etc, or don't you handle those?

--
   Richard Huxton
   Archonet Ltd

pgsql-general by date:

Previous
From: Dennis Bjorklund
Date:
Subject: Re: index row size exceeds btree maximum, 2713 - Solutions?
Next
From: "Ilja Golshtein"
Date:
Subject: Re: Old question - failed to find conversion function from "unknown"