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?
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
String can also be queried by using casting: db.session.query(Post).filter(Post.tags.contains(cast([tag], ARRAY(String))). Imports required: from sqlalchemy import String, from sqlalchemy.dialects.postgresql import ARRAY