Re: array_agg cast issue

Peter Neave <Peter(dot)Neave(at)jims(dot)net> writes:
> We moved our development database from Windows (PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit to) to Linux (PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit). When using pgAdmin 4 (v1.4) and DataGrip I get an occasional error message as I’m clicking on table names.

> For example
> ERROR: function array_agg(bigint) is not unique
> Hint: Could not choose a best candidate function. You might need to add explicit type casts.

[ squint... ] That should not be possible unless something's mucked up
the available set of functions. In a standard 9.6 database, there are
two versions of array_agg:

regression=# \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)

but only one of those could match any given call with a defined argument
type --- certainly a bigint argument couldn't match the first one.

9.0 was different:

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

I'm suspicious that your DB contains some hacked-up definition for
array_agg() that worked with 9.0 but not so well for 9.6. I don't
know what DataGrip is, maybe it needs an update?

regards, tom lane