Quantcast

array_agg cast issue

Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

array_agg cast issue

Peter Neave

Hi,

 

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.

    Position: 255.

   (439ms)

 

 

For example, when I’m in pgAdmin and I click on a view with the “SQL” tab on I get

  ERROR: function array_agg(text) is not unique
  LINE 16: array_agg(provider || '=' || label)
  ^
  HINT: Could not choose a best candidate function. You might need to add explicit type casts.

 

 

I also get a similar issue with this query which seems to work fine on other databases created freshly on 9.6.1.

  select array_agg(1::bigint);

 

The obvious thing to do is to create a cast – how do I do that? Has there been a change in the default casts from 9.0 to 9.6.1?

 

Peter


 

Peter Neave Software Developer | [hidden email]

48 Edinburgh Rd | Mooroolbark | VIC, 3138
P 1300 130 490 | Intl +61 3 8419 2910


Our priority is the welfare of our Franchisees.
Our aim is to sign Franchisees and Franchisors we are convinced will succeed.
Our mantra is passion for providing great customer service.

Jim Penman - Founder, Jim's Group
Jim's Group Mantra
JIm's Group - Established 1989

This email and any attachment(s) are confidential. If you are not the intended recipient you must not copy, use, disclose, distribute or rely on the information contained in it. If you have received this email in error, please notify the sender immediately by reply email and delete the email from your system. Confidentiality and legal privilege attached to this communication are not waived or lost by reason of mistaken delivery to you. While Jim's Group employs Anti-Virus Software, we cannot guarantee that this email or the attachment(s) are unaffected by computer virus, corruption or other defects and we recommend that this email and any attachments be tested before opening.

Please consider the environment before printing this email.




Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: array_agg cast issue

Tom Lane-2
Peter Neave <[hidden email]> 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


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: array_agg cast issue

Peter Neave
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!


 

Peter Neave Software Developer | [hidden email]

48 Edinburgh Rd | Mooroolbark | VIC, 3138
P 1300 130 490 | Intl +61 3 8419 2910


Our priority is the welfare of our Franchisees.
Our aim is to sign Franchisees and Franchisors we are convinced will succeed.
Our mantra is passion for providing great customer service.

Jim Penman - Founder, Jim's Group
Jim's Group Mantra
JIm's Group - Established 1989

This email and any attachment(s) are confidential. If you are not the intended recipient you must not copy, use, disclose, distribute or rely on the information contained in it. If you have received this email in error, please notify the sender immediately by reply email and delete the email from your system. Confidentiality and legal privilege attached to this communication are not waived or lost by reason of mistaken delivery to you. While Jim's Group employs Anti-Virus Software, we cannot guarantee that this email or the attachment(s) are unaffected by computer virus, corruption or other defects and we recommend that this email and any attachments be tested before opening.

Please consider the environment before printing this email.


-----Original Message-----
From: Tom Lane [mailto:[hidden email]]
Sent: Friday, 19 May 2017 4:11 PM
To: Peter Neave <[hidden email]>
Cc: [hidden email]
Subject: Re: [NOVICE] array_agg cast issue

Peter Neave <[hidden email]> 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



Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: array_agg cast issue

Tom Lane-2
Peter Neave <[hidden email]> writes:
> For some reason when I restored a version of array_agg was brought with it.
> ...
> I simply ran
> DROP AGGREGATE public.array_agg(anyelement);
> and my problem has been fixed.

Ah, cool.  You might want to check and see if any other unwanted copies
of system functions are there too ...

                        regards, tom lane


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Next Thread
Loading...