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?

Normalize your data model. Then it gets as simple as creating a unique index — user330315
– user330315, Commented Nov 24, 2020 at 7:45
@a_horse_with_no_name Could you elaborate on this please? You are suggesting to introduce a user_group table that will connect user to group and avoid using array field, correct? So you can select group by JOINing with user_table and order records by user_id, but how would you index and put UNIQUE constraint on it? Thanks — andnik
– andnik, Commented Nov 24, 2020 at 7:58

Gordon Linoff · Accepted Answer · 2020-11-24 13:24:48Z

2

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));

answered Nov 24, 2020 at 13:24

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

andnik Over a year ago

good point. I am planing to sort array in the backend. I wonder more about how would you handle such situation, what data model alternative you can suggest. Thanks.

Rodrigo Lima · Accepted Answer · 2020-11-28 18:23:46Z

1

+50

You cold normalize that, making another table like an user_group (you already discussed about it)

But to make it unique you could use a funcion or a procedure to create a new user_group, this is not the best solution to create a new user_group but it makes better to handle these information after it's created.

It depends on how often you create vs manipulate those data.

answered Nov 28, 2020 at 18:23

Rodrigo Lima

1035 bronze badges

2 Comments

andnik Over a year ago

Good point! Thank you @Rodrigo. I decided to go with my initial approach, having a sorted array with UNIQUE constraint + I would still probably have user_group table with data duplication, but I think this can justify the simplicity and possibly the speed of this type of design and I will avoid introducing DB function/procedure as another layer of business logic. Makes sense, what do you think?

Rodrigo Lima Over a year ago

Yes, its good to avoid as much as possible to use functions or procedures, but in some particular times it's better to use it, because backend can make a mess in your database like this case. You must always think what's the better solution for your case and sometimes you can't avoid drawbacks than you choose the lesser evil. Finaly, if your approach has no inconsistency or other huge negative impact there is no problem using it even with data duplication.

Collectives™ on Stack Overflow

PostgreSQL: Optimal way to store and index Unique Array field

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related