pythonpostgresqlpython-3.xsqlalchemyflask-sqlalchemy

Create a Full Text Search index with SQLAlchemy on PostgreSQL


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)

Solution

  • 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
    from sqlalchemy.sql import func
    
    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()