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.

7
  • 2
    Unrelated, but: distinct is not a function. distinct id is the same as distinct (id) Commented Apr 20, 2017 at 15:49
  • 1
    Your example works for me with 9.6.2: See here: i.imgur.com/PgaAkRD.png Commented Apr 20, 2017 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 Commented Apr 20, 2017 at 15:55
  • It works on a Linux based installation for me as well: i.imgur.com/C7Td0OV.png Commented Apr 20, 2017 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. Commented Apr 20, 2017 at 15:57

2 Answers 2

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.

Sign up to request clarification or add additional context in comments.

1 Comment

I'm having the same issue. Can you explain what this function does? Also - how do you drop this function?
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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.