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

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...

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...