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
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)
