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?

Mullens

Answer 1

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))

Mullens

Answer 2

It turns out that it is now possible to do this with the autogenerator. When declaring your table (I'm using SQLAlchemy Core, but you could no doubt adapt this to ORM style):

import sqlalchemy as sa

metadata = sa.MetaData()

sa.Table(
    "table_name",
    metadata,
    # ... list of columns and other indexes ...
    sa.Index(
        "ix_index_name",
        sa.sql.expression.text("to_tsvector('english'::regconfig, keywords)"),
        postgresql_using="gin",
    )
)

Sunset

Answer 3

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}\')')])

Bookout

Thank you, so true! I fixed the question to be consistent with the answer. Good to know about the btree queries, too. – Mullens