[postgis-devel] why this difference when sub-query

Tom Lane tgl at sss.pgh.pa.us
Mon Jul 13 11:02:23 PDT 2009

nicklas.aven at jordogskog.no writes:
> if I run:
> select st_astext(geom1) from
> (select NULL as geom1) a;
> I get:

> FEIL:  failed to find conversion function from unknown to geometry

The reason for that is that the output column types of the sub-query
have to be identified, and since there is no available information
about the type of the NULL, it gets marked as "unknown", which is a
type that has no operations.  Then the outer query needs to convert
to geometry to feed the function, and there isn't a conversion to
do that.  You could write something like

	select st_astext(geom1) from
	(select NULL::geometry as geom1) a;

to avoid this.

As an aside, I believe that the SQL standard doesn't allow a bare NULL
like that at all -- per spec, you are *always* required to cast it to
some identifiable type immediately.  Postgres and most other SQL
implementations are a bit laxer, but you can still run into trouble
if there isn't some pretty-nearby context that determines what type
the NULL constant should be considered to be.  For PG, "nearby"
generally means "inside the same subquery".

			regards, tom lane

More information about the postgis-devel mailing list