PostgreSQL: Optimal way to store and index Unique Array field

Question

I have user table with id as GUID and I want to create group table for users.

Constraint: group table row should be unique for a set of users?

Meaning if I want to create a new group previously I need to check if such a group already exists, meaning I need to check if my array of user GUIDs already exists in group table.

Here is a schema approximation where I use Array of UUID and Unique constraint:

CREATE TABLE user (
   id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
   ...
)

CREATE TABLE group (
   ...
   users_array UUID[] UNIQUE,
   ...
)

When I create a new group I check if there is a group for the current sorted set of users, if there is no such group I create a new record if there is then I just retrieve the existing group.

Do you think UUID[] UNIQUE is the best solution for search and what would be the alternatives?

Answer 1

I'm not sure this is the best representation of the data. But you want a unique condition on an array. The problem is that arrays in different orders are not the same.

And, unfortunately, Postgres does not have an "array sort" function. Of course, this is easy enough to do in SQL using array_agg() and unnest(). But those are not allowed in an index definition

But user-defined functions are. So, start with:

create function array_sort(anyarray) returns anyarray as $$
    select array_agg(distinct n order by n)
    from unnest($1) as t(n);
$$ language sql immutable;

Then you can use this in a unique constraint:

create unique constraint unq_groups_users on groups(array_sort(users));