Thread: Arrays - selecting (and not removing) duplicates...

Hi all,

Suppose I have two arrays (TEXT, INT - not important - prefer INT) as follows:

x = {1, 4, 5, 6, 6, 7, 8, 9, 9, 9, 9, 10, 12, 12}

I would like a result of:

y = {6, 9, 12}

I'm aware of the UNNEST function and that I can convert the array into
rows and do a COUNT(blah) HAVING COUNT(blah) > 1 and then ARRAY_AGG
back.

I was just wondering if there is some (already written) function out
there that will drop this functionality into my lap?

TIA and Rgs,


Pól...

On Mon, Aug 30, 2021 at 01:47:19PM +0100, Pól Ua Laoínecháin wrote:
> I was just wondering if there is some (already written) function out
> there that will drop this functionality into my lap?

Sure, here:

#v+
create function array_dups(ANYARRAY) returns ANYARRAY as $$
    select array( select i from unnest($1) i group by i having count(*) > 1 );
$$ language sql;
#v-

Best regards,

depesz

Hi Hubert, and thanks for your input!

> > I was just wondering if there is some (already written) function out
> > there that will drop this functionality into my lap?

> #v+
> create function array_dups(ANYARRAY) returns ANYARRAY as $$
>     select array( select i from unnest($1) i group by i having count(*) > 1 );
> $$ language sql;
> #v-

I'm a bit of a novice with PostgreSQL functions I'm afraid.

I can call your function this way:

SELECT ARRAY_DUPS(ARRAY[1, 2, 3, 4, 4]) AS dups;

and the answer is

dups
{4}

as I would expect. I can also do this:

SELECT ARRAY[1,3,4,1, 5,6,5, 67]  AS my_nums;
answer is
my_nums
array{1,3,4,1,5,6,5,67}

But, when I do this:

SELECT ARRAY_DUPS(SELECT ARRAY[1,3,4,1, 5,6,5, 67])

I get an error:

ERROR: syntax error at or near "SELECT" LINE 1:
SELECT ARRAY_DUPS(SELECT ARRAY[1,3,4,1, 5,6,5, 67])


I want to "feed" the result of a query into your function - is there
something special I have to do for this?

TIA and rgs,


Pól...

> depesz

Hi all and apologies for the simple question!

> I want to "feed" the result of a query into your function - is there
> something special I have to do for this?

SELECT
  ARRAY_DUPS
  (
    (
      SELECT ARRAY[1,3,4,1, 5,6,5, 67]
    )
  ) AS t;

answer:

t
{1,5}


And I can also feed in queries. A second set of braces (round
brackets) is needed!

Thanks again and rgs,


Pól...

> Pól...