function array_agg(text) is not unique when connecting with pgAdmin III

Question

When I connect to PostgreSQL database with pgAdmin III (1.22.2), I get this error:

ERROR:  function array_agg(text) is not unique
LINE 5: (SELECT array_agg(label) FROM pg_shseclabel sl1 WHERE sl1.ob...
                 ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

The database server is PostgreSQL 9.6.

Same instance of pgAdmin works fine with databases running PostgreSQL 9.0 versions.

How can I fix this problem?

Answer 1

I found that problem is caused by migration from 9.0 to 9.6.

Log in to the database using psql and run \df array_agg. This is how it should look in 9.0:

db=# \df array_agg
                           List of functions
   Schema   |   Name    | Result data type | Argument data types | Type 
------------+-----------+------------------+---------------------+------
 pg_catalog | array_agg | anyarray         | anyelement          | agg
(1 row)

and in 9.6:

db=# \df array_agg
                           List of functions
   Schema   |   Name    | Result data type | Argument data types | Type 
------------+-----------+------------------+---------------------+------
 pg_catalog | array_agg | anyarray         | anyarray            | agg
 pg_catalog | array_agg | anyarray         | anynonarray         | agg
(2 rows)

However, when I ran \df array_agg on my server, I get:

db=> \df array_agg
                                Lista funkcji
  Schemat   |   Nazwa   | Typ danych wyniku | Typy danych argumentów |  Typ  
------------+-----------+-------------------+------------------------+-------
 pg_catalog | array_agg | anyarray          | anyarray               | agreg
 pg_catalog | array_agg | anyarray          | anynonarray            | agreg
 public     | array_agg | anyarray          | anyelement             | agreg
(3 rows)

As mentioned here, it can be fixed by:

DROP AGGREGATE public.array_agg(anyelement);

Answer 2

In my case, explicitly typecasting the argument to either TEXT or VARCHAR works.

E.g.,

SELECT array_agg('Group1');

Results in:

ERROR:  function array_agg(unknown) is not unique
LINE 1: SELECT array_agg('Group1');
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
SQL state: 42725
Character: 8

However, both of

SELECT array_agg('Group1'::VARCHAR);

and

SELECT array_agg('Group1'::TEXT);

work as expected and return the respective TEXT or VARCHAR array.