PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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()]
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search