Re: [NOVICE] array_agg cast issue - Mailing list pgsql-novice

Thanks!

For some reason when I restored a version of array_agg was brought with it.

                          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
public     | array_agg | anyarray         | anyelement          | agg
(3 rows)

I simply ran
DROP AGGREGATE public.array_agg(anyelement);
and my problem has been fixed.

Thanks Tom!

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, 19 May 2017 4:11 PM
To: Peter Neave <Peter.Neave@jims.net>
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] array_agg cast issue

Peter Neave <Peter.Neave@jims.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
--
Message  protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au

Click here to report this message as spam:
https://console.mailguard.com.au/ras/1QT7bmws14/51fE4gEkYaw4NbotS6tk1y/0