Can't use postgresql_ops for expressions
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, '')))
Comments (9)
-
repo owner -
reporter That indeed works correctly.
-
repo owner - attached test.py
-
repo owner 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.
-
repo owner - changed status to duplicate
Duplicate of
#3174. -
repo owner was fixed in 0.9.9
-
reporter - attached issue3432.py
-
reporter 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
andbtree_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. -
repo owner 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!
- Log in to comment
any chance you can just use text()?