SQLAlchemy and Postgresql: to_tsquery()

Question

Does anyone know how to use to_tsquery() function of postgresql in sqlalchemy? I searched a lot in Google, I didn't find anything that I can understand. Please help.

I hope it is available in filter function like this:

session.query(TableName).filter(Table.column_name.to_tsquery(search_string)).all()

The expected SQL for the above query is something like this:

Select column_name
    from table_name t
    where t.column_name @@ to_tsquery(:search_string)

Answer 1

My fifty cents in 2021, following the docs

None of the previous answers mention how to cast the text column as postgres: to_tsvector('english', column). I have a text column indexed as tsvector. And this is the way:

select(mytable.c.id).where(
        func.to_tsvector('english', mytable.c.title )\
        .match('somestring', postgresql_regconfig='english')
    )

In my case, I didn't want to use to_tsquery as ".match" forces. A more intuitive way is to use websearch_to_tsquery when you have a search input like stackOverflow. So I did a mix from jd response.

I finally applied it as a .filter() the following statement:

func.to_tsvector('english', Table.column_name)\
    .op('@@')(func.websearch_to_tsquery("string to search",
    postgresql_regconfig='english'))

I think this is the general formula and it applies to to_tsquery too.

Answer 2

This may have been added recently but worth adding as a more standard solution.

query.filter(Model.attribute.match('your search string'))

does it for you as it looks for the right operation available for your dialect.

See the official docs: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#full-text-search

Of course, this assumes the table you are querying is a view built with a to_tsvector attribute to apply the @@ operation to.