Re: [NOVICE] array_agg cast issue

pinker <pinker@onet.eu> writes:
> I took a query from  wiki
> <https://wiki.postgresql.org/wiki/Index_Maintenance#Duplicate_indexes>  :

> SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
>        (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
>        (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
> FROM (
>     SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'||
> indclass::text ||E'\n'|| indkey::text ||E'\n'||
>                                          COALESCE(indexprs::text,'')||E'\n'
> || COALESCE(indpred::text,'')) AS KEY
>     FROM pg_index) sub
> GROUP BY KEY HAVING COUNT(*)>1
> ORDER BY SUM(pg_relation_size(idx)) DESC;

> *and the result:*
> ERROR:  function array_agg(regclass) is not unique
> LINE 2:        (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS i...
>                 ^
> HINT:  Could not choose a best candidate function. You might need to add
> explicit type casts.

That example works fine for me.  Maybe you have an extra user-defined
function named "array_agg"?  Doing "\df array_agg" in psql might be
informative.
        regards, tom lane


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice