Re: [NOVICE] array_agg cast issue

I would like to refresh the topic.

I have the same issue on PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

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.
Stan SQL: 42725
Znak: 76


What helps it's adding a schema before function:
public.array_agg

But it's probably not a desired behavior?








--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html


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