How to create multiple index with sqlalchemy?

How to create jsonb index using GIN on SQLAlchemy?

• Here's current code creating index for JSONB. Index("mytable_data_idx_id_key", Mytable.data['id'].astext, postgresql_using='gin') But I got this error. sqlalchemy.exc.ProgrammingError: (psycopg2.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. [SQL: "CREATE INDEX event_data_idx_id_key ON event USING gin ((data ->> 'id'))"] Is there any way to create index on SQLAlchemy?

• Answer:

  The PostgreSQL specific SQLAlchemy docs at http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention a postgresql_ops dictionary to provide the "operator class" used by PostgreSQL, and provide this example illustrating its use: Index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops', 'id': 'int4_ops' }) From experimenting, it seems that you need to use a text() index description if you want to specify the "operator class" for an expression index. So, db.Index( 'ix_sample', sqlalchemy.text("(jsoncol->'values') jsonb_path_ops"), postgresql_using="gin") ...in __table_args__ for an ORM model specifies a GIN index on a jsonb field that contains an array of strings, and that allows for efficient lookups, i.e. matching on any of the strings in the JSON array field that looks like this: { "values": ["first", "second", "third"], "other": "fields", "go": "here" } Querying using the @> operator in PostgreSQL would look something like this: import sqlalchemy from sqlalchemy.dialects import postgresql query = session.query(MyModel).filter( sqlalchemy.type_coerce(MyModel.jsoncol['values'], postgresql.JSONB) .contains(sqlalchemy.type_coerce("second", postgresql.JSONB))) results = query.all()