The unique index of an ArrayField regardless of the position of elements in it

Question

class Marriage(BaseModel):
    users = ArrayField(BigIntegerField)
    chat_id = BigIntegerField()

    class Meta:
        indexes = (
            (('users', "chat_id"), True),
        )

I would like to make a unique index for both user values and chat_id for example:

there is already such a row in the database:

users=[1,2], chat_id=1

I would like to create the same but a different position in the user array.

users=[2,1], chat_id=1

I need this "duplicate key value violates" to work as well, how can I do this?

Answer 1

I'd suggest consulting the Postgres folks as I'm not sure how you'd do that. I think the "best" solution is to store them flat:

class Marriage(BaseModel):
    chat_id = pw.BigIntegerField()
    user_id = pw.BigIntegerField()
    class Meta:
        indexes = (
            (('chat_id', 'user_id'), True),
        )

for uid in (1, 2):
    Marriage.create(user_id=uid, chat_id=1)

Then to get all users for a marriage:

q = Marriage.select(Marriage.user_id).where(Marriage.chat_id == 1)
uids = [uid for uid, in q.tuples()]

Answer 2

In Postgres you create a unique index and order the values of (user_id, chat_id) using the least(), greatest() functions as follows: (see demo)

create unique  index chat_user_same_ndx 
    on marriage( least(chat_id, user_id), greatest(chat_id, user_id) );

I am not sure how this exactly translates into your obscurification language. But would seem doable looking how you created the index in your code.