Create a Full Text Search index with SQLAlchemy on PostgreSQL

Question

I need to create a PostgreSQL Full Text Search index in Python with SQLAlchemy. Here's what I want in SQL:

CREATE TABLE person ( id INTEGER PRIMARY KEY, name TEXT ); CREATE INDEX person_idx ON person USING GIN (to_tsvector('simple', name)); 

Now how do I do the second part with SQLAlchemy when using the ORM:

class Person(db.Model):     id = db.Column(db.Integer, primary_key=True)     name = db.Column(db.String) 

Answer 1

You could create index using Index in __table_args__. Also I use a function to create ts_vector to make it more tidy and reusable if more than one field is required. Something like below:

from sqlalchemy.dialects import postgresql  def create_tsvector(*args):     exp = args[0]     for e in args[1:]:         exp += ' ' + e     return func.to_tsvector('english', exp)  class Person(db.Model):     id = db.Column(db.Integer, primary_key=True)     name = db.Column(db.String)      __ts_vector__ = create_tsvector(         cast(func.coalesce(name, ''), postgresql.TEXT)     )      __table_args__ = (         Index(             'idx_person_fts',             __ts_vector__,             postgresql_using='gin'         )     ) 

---

Update: A sample query using index (corrected based on comments):

people = Person.query.filter(Person.__ts_vector__.match(expressions, postgresql_regconfig='english')).all()