Can't use postgresql_ops for expressions

[Wichert Akkerman]

I need to build a trigram index for a set of columns in PostgreSQL. The SQL statement looks like this:

CREATE INDEX trgm_idx ON my_table USING gist 
(language, (coalesce(col1, '') || ' ' || coalesce(col2, '')) gist_trgm_ops);

My attempt to do this using SQLAlchemy looks like this:

schema.Index('trgm_idx', 
    Mytable.language,
    (func.coalesce(Mytable.col1, '') + ' ' + func.coalesce(MyTable.col2, '')).label('foo'),
    postgresql_ops={
        'foo': 'pg_trgm',
    },
    postgresql_using='gist')

I use label() just to get a key I can pass to postgresql_ops. Unfortunately this does not work. This is the generated SQL:

CREATE INDEX trgm_idx ON my_table USING gist (language, (coalesce(col1, '') || ' ' || coalesce(col2, '')))

[Mike Bayer]

any chance you can just use text()?

>>> from sqlalchemy import Index,text        
>>> from sqlalchemy.schema import CreateIndex
>>> from sqlalchemy.dialects import postgresql
>>> from sqlalchemy import MetaData, Table, Column, Integer
>>> idx = Index(
...     'trgm_idx', 
...     text("language, (coalesce(col1, '') || ' ' || coalesce(col2, '')) gist_trgm_ops"),
...     postgresql_using='gist')
>>> m = MetaData()
>>> t = Table('xyz', m, Column('col1', Integer), Column('col2', Integer))
>>> t.append_constraint(idx)
>>> print CreateIndex(idx).compile(dialect=postgresql.dialect())
CREATE INDEX trgm_idx ON xyz USING gist (language, (coalesce(col1, '') || ' ' || coalesce(col2, '')) gist_trgm_ops)

[Wichert Akkerman]

That indeed works correctly.

[Mike Bayer]

So I'm not sure how to make a full round-trip example work here since I don't know GIST/operator classes very well, but all elements here render for me and also get sent to the DB in a live test, can you review the attached test please ? thanks.

[Mike Bayer]

Duplicate of #3174.

[Mike Bayer]

was fixed in 0.9.9

[Wichert Akkerman]

The test was not quite correct: a trigram index is used on a text column, not a tsvector. Generally you use a trigram index for language where you can't use tokenisation (most Asian languages) or if you want to handle spelling errors. In order for the test to succeed the pg_trgm and btree_gist extensions must be loaded.

I've attached an updated version of the test which switches the columns to Text and which runs successfully for me.

[Mike Bayer]

great. yeah i was going for a 30 second solution and at http://www.postgresql.org/docs/9.3/static/textsearch-indexes.html it said "The column can be of tsvector or tsquery type.", but i guess the ops is significant here, OK!