Could not determine polymorphic type because input has type "unknown"

Question

I have a query which gives output as

Could not determine polymorphic type because input has type "unknown"

Query :

select ( array_to_string(array_agg(name), ', '))::text as name,path 
from(select 'fullpath' as Path,null as id,'' as name 
     from tblabc where key = 'key1' and value = '1' 
    ) as e 
group by path;

I have a postgres database

Answer 1

The issue here is that '' as name doesn't actually specify a type for the value. It's the unknown type, and PostgreSQL usually infers the real type from things like what column you're inserting it into or what function you pass it to.

In this case, you pass it to array_agg, which is a polymorphc function. It can take inputs of the pseudo-type anyelement, which really just means "figure it out at runtime".

PostgreSQL would still figure it out except that array_to_string doesn't actually take a text[] as input. It takes anyarray - another polymorphic type, like anyelement for arrays.

So there's nothing in the query to tell PostgreSQL what type that '' is. It could guess you meant text, but it's a bit too fussy for that. So it complains. The issue simplifies down to:

regress=> SELECT array_to_string(array_agg(''), ',');
ERROR:  could not determine polymorphic type because input has type "unknown"

To solve this, write a typed literal:

TEXT '' AS name

or use a cast:

CAST('' AS text) AS name

or the PostgreSQL shorthand:

''::text

examples:

regress=> SELECT array_to_string(array_agg(TEXT ''), ',');
 array_to_string 
-----------------

(1 row)

regress=> SELECT array_to_string(array_agg(''::text), ',');
 array_to_string 
-----------------

(1 row)

regress=> SELECT array_to_string(array_agg(CAST('' AS text)), ',');
 array_to_string 
-----------------

(1 row)