6

I have a query that use array_agg with distinct as an argument and is not accepted on postgres 9.6.

I created this sample to illustrate the issue:

create table numbers (id integer primary key, name varchar(10));
insert into numbers values(1,'one');
insert into numbers values(2,'two');

postgres 9.4

select array_agg(distinct(id)) from numbers;
 array_agg 
-----------
 {1,2}

postgres 9.6

ERROR:  function array_agg(integer) is not unique
LINE 1: select array_agg(distinct(id)) from numbers;
               ^
HINT:  Could not choose a best candidate function. 
You might need to add explicit type casts.

What do I need to change in order to get this result on postgres 9.6?

Thanks.

This is what I get checking the functions: 

nspname | proname | proargtypes 
------------+-----------+--------------------- 
pg_catalog | array_agg | [0:0]={anyarray} 
public | array_agg | [0:0]={anyelement} 
pg_catalog | array_agg | [0:0]={anynonarray

Now, I found the issue thanks to the comment by pozs. I remove the public definition of the aggregated function and it worked.

The issue was just on the database that I was working on, as I found some people saying that the sample worked for them I created a new database an run the example. And then the only change there was the aggregate function definitions.

Community
  • 1
  • 1
Browser_80
  • 131
  • 1
  • 5
  • 2
    Unrelated, but: `distinct` is **not** a function. `distinct id` is the same as `distinct (id)` –  Apr 20 '17 at 15:49
  • 1
    Your example works for me with 9.6.2: See here: https://i.imgur.com/PgaAkRD.png –  Apr 20 '17 at 15:50
  • thanks for your response, Ok, this is what I'm using exactly: PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit – Browser_80 Apr 20 '17 at 15:55
  • It works on a Linux based installation for me as well: https://i.imgur.com/C7Td0OV.png –  Apr 20 '17 at 15:57
  • 1
    Maybe a UDF interferes? You can check that by listing all `array_agg` named function of yours, [f.ex. with this](http://rextester.com/MLHSEL68822). – pozs Apr 20 '17 at 15:57
  • This is what I get checking the functions: nspname | proname | proargtypes ------------+-----------+--------------------- pg_catalog | array_agg | [0:0]={anyarray} public | array_agg | [0:0]={anyelement} pg_catalog | array_agg | [0:0]={anynonarray} – Browser_80 Apr 20 '17 at 16:02
  • @Browser_80 update the question with that. – Evan Carroll Apr 20 '17 at 16:40

2 Answers2

7

Now, I found the issue thanks to the comment by pozs. I remove the public definition of the aggregated function and it worked.

The issue was just on the database that I was working on, as I found some people saying that the sample worked for them I created a new database an run the example. And then the only change there was the aggregate function definitions.

So I drop the function public | array_agg | [0:0]={anyelement} and it worked.

Thanks a lot.

Browser_80
  • 131
  • 1
  • 5
0

It works exactly like that as demonstrated by this dbfiddle on PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit.

enter image description here

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468