Обсуждение: Arrays - selecting (and not removing) duplicates...

Поиск
Список
Период
Сортировка

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

От
Pól Ua Laoínecháin
Дата:
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...



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

От
hubert depesz lubaczewski
Дата:
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




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

От
Pól Ua Laoínecháin
Дата:
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



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

От
Pól Ua Laoínecháin
Дата:
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...