Postgres gin index requires to_tsvetor

[Former user]

(original reporter: d0ugal) When using

Index('content_index', 'content', postgresql_using="gin")

I get

sqlalchemy.exc.ProgrammingError: (ProgrammingError) data type text has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
 'CREATE INDEX content_index ON post USING gin (content)' {}

because postgres expects something like

CREATE INDEX content_index ON post USING gin(to_tsvector('english', content));

http://www.postgresql.org/docs/9.1/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX

[Former user]

I think the requested feature here is really the ability to use functions in an index definition, which would make it a duplicate of #695.

[Former user]

(original author: d0ugal) After some discussion in #sqlalchemy on freenode, I think that's correct.

However, It may be that it should be considered a documentation flaw. The example if the documentation doesn't work unless my_table.c.data is a tsvector. Or, perhaps, an additional note on how to handle it as not being a tsvector would be useful.

http://docs.sqlalchemy.org/en/rel_0_7/dialects/postgresql.html#index-types

For now I'm handling this with events and DDL.

[Former user]

It could be any data type that supports the GIN access methods, which include hstore and arrays. Additionally, you can always create a new column on your table of type tsvector, and use that instead. You can keep it updated by using a trigger, for instance.

Of course, GIN is not the only possibility there. You may supply hash, btree, gin, gist, or spgist.

[Former user]

(original author: d0ugal) Sure, that's basically what I'm doing.

But yeah, maybe this can just be closed. I think its just a dupe of #695 although I think the docs could do with some more detail on it too.

[Mike Bayer]

kind of a dupe of that one, yes. that the "to_tsvector()" clause doesn't emit with postgresql_using="gin" is an extension of that issue.