Get unique values from PostgreSQL array

Question

This seems like it would be straightforward to do but I just can not figure it out. I have a query that returns an ARRAY of strings in one of the columns. I want that array to only contain unique strings. Here is my query:

SELECT
    f."_id",
    ARRAY[public.getdomain(f."linkUrl"), public.getdomain(f."sourceUrl")] AS file_domains,
    public.getuniqdomains(s."originUrls", s."testUrls") AS source_domains
FROM
    files f
LEFT JOIN
    sources s
ON
    s."_id" = f."sourceId"

Here's an example of a row from my return table

_id	file_domains	source_domains
2574873	{cityofmontclair.org,cityofmontclair.org}	{cityofmontclair.org}

I need file_domains to only contain unique values, IE a 'set' instead of a 'list'. Like this:

_id	file_domains	source_domains
2574873	{cityofmontclair.org}	{cityofmontclair.org}

Laurenz Albe · Accepted Answer · 2021-09-10 05:35:23Z

1

Use a CASE expression:

CASE WHEN public.getdomain(f."linkUrl") = public.getdomain(f."sourceUrl")
     THEN ARRAY[public.getdomain(f."linkUrl")]
     ELSE ARRAY[public.getdomain(f."linkUrl"), public.getdomain(f."sourceUrl")]
END

answered Sep 10, 2021 at 5:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Get unique values from PostgreSQL array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related