How to create a Postgres GIN index with a config using SQLAlchemy?

Question

I have manually created a GIN index on a Postgres table as follows:

create index idx_ib_keywords on stuff using gin(to_tsvector('english'::regconfig, keywords));

It gets created fine:

\d info_block_template
                 Table info_block_template
   Column   | Type   | Collation | Nullable | Default
------------+--------+-----------+----------+--------
.
.
.
 keywords   | text   |           | not null | 
.
.
.
Indexes:
    .
    .    "idx_ib_keywords" gin (to_tsvector('english'::regconfig, keywords))

Now, I am using alembic for migrations. When I autogenerate a migration with alembic, the GIN index is NOT automatically generated. No worries, the automatic generator is not supposed to be perfect. So I want to go in and hand-edit the migration file.

I have searched for how to do this, and the closest thing I could find was this page which I followed, and wrote

op.create_index(op.f('idx_ib_keywords'), 'stuff', ['keywords'], postgresql_using='gin')

into my migration script. When I apply this migration, I get the error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) 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.

That's a great error message; it's telling me I need to do the to_tsvector thing. However, I don't know how to do that in SQLAlchemy.

Is there an easy way to write this in SQLAlchemy, or should I drop into raw SQL in the migration file?

user_831878 · Answer

It turns out that the additional information is specified as a functional index, not as part of the postgresql_using kwarg.

The correct SQLAlchemy statement is:

op.create_index(op.f('idx_ib_keywords'),
                'info_block_template',
                [sa.text('to_tsvector(\'english\'::regconfig, keywords)')],
                postgresql_using='gin')

When applying this migration, the newly created index will show up exactly as desired:

"idx_ib_keywords" gin (to_tsvector('english'::regconfig, keywords))

user_1168821 · Answer

I got this to work with the help of your solution. It might be worth mentioning the 'info_block_template' is the table name as in the sample above it's 'stuff'. I wasn't sure if it was some special syntax.

It also works well for btree queries for indexes. e.g.

op.create_index(op.f('ix_index_name'), 'table_name',
            [sa.text('(jsonb_column_name #>> \'{top_level_object,second_level}\')')])

How to create a Postgres GIN index with a config using SQLAlchemy?

2 Answers