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

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

Answers


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)

Need Your Help

How to create custom keyboard shortcuts for google app script functions?

google-apps-script google-sheets keyboard-shortcuts

I'm trying to get a sense of the viability of replacing some of my Microsoft Excel spreadsheets with Google Doc Spreadsheets. How can I create a custom keyboard shortcut to a google app script fun...

Check if an apt-get package is installed and then install it if it's not on Linux?

bash shell apt-get

I'm working on a Ubuntu system and currently this is what I'm doing: