How to create GIN index on text array column in SQLAlchemy (with PostgreSQL and python)

Question

I want to perform large number of queries to filter by tag, on a postgre table

from sqlalchemy.dialects.postgresql import ARRAY

class Post(db.Model):
    __tablename__ = 'post'

    id = db.Column(db.Integer, primary_key=True)
    tags = db.Column(ARRAY(db.String))

This link recommends storing tags as text array with a GIN index.

How do I add GIN index to above table? Also does it make a difference whether I use String vs Text datatype?

Answer 1

I solved it by following:

from sqlalchemy.dialects.postgresql import ARRAY, array

class Post(db.Model):
    __tablename__ = 'post'

    id = db.Column(db.Integer, primary_key=True)
    tags = db.Column(ARRAY(db.Text), nullable=False, default=db.cast(array([], type_=db.Text), ARRAY(db.Text)))
    __table_args__ = (db.Index('ix_post_tags', tags, postgresql_using="gin"), )

And query simply by

db.session.query(Post).filter(Post.tags.contains([tag]))

Have to keep the array type to Text and not String otherwise some error happens