Subject: How to remove duplicates in an array and maintain the order?

From: Shaozhong SHI <[email-masked]>
Date: Sat, 8 Jul 2023 16:30:31 +0100

How to remove duplicated values in an array and maintain the order?

Regards,

David

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 8 Jul 2023 08:36:19 -0700

> How to remove duplicated values in an array and maintain the order?

Use a for loop in pl/pgsql.

David J.

---

From: Marcos Pegoraro <[email-masked]>
Date: Sat, 8 Jul 2023 12:50:54 -0300

> select array_agg(unnest) filter (where row_number = 1) from (select
unnest, ordinality, row_number() over(partition by unnest order by
ordinality) from (select * from
unnest('{7,7,5,6,2,2,3,8,5,4,5}'::integer[]) with ordinality) x order by
ordinality) x

{7,5,6,2,3,8,4}

regards
Marcos

---

From: Thomas Kellerer <[email-masked]>
Date: Sat, 8 Jul 2023 18:19:10 +0200

> How to remove duplicated values in an array and maintain the order?

You can use distinct on ()

select array_agg(val order by idx)
from (
   select distinct on (val) val, idx
   from unnest(array[7,7,5,6,2,2,3,8,7,5,4,1,5]) with ordinality as t(val,idx)
   order by val, idx
) x

This picks the first occurrance of each element.
If you want to get the last occurrance of each value use "order by val, idx desc" in the inner select