Search code examples
postgresqlsqlalchemyalembic

Create a DB index if it doesn't exist

I have an Alembic migration which creates a few DB indexes that were missing in a database. Example:

op.create_index(op.f('ix_some_index'), 'table_1', ['column_1'], unique=False)

However, the migration fails in other environments that already have the index: 

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "ix_some_index" already exists

PostgreSQL supports an IF NOT EXISTS option for cases like this, but I don't see any way of invoking it using either Alembic or SQLAlchemy options. Is there a canonical way of checking for an existing index?

Solution

  • Currently, you can pass if_not_exists argument to create_index function in Alembic as:

    op.create_index('ix_some_index', 'table_1', ['column_1'], unique=False, if_not_exists=True)

    Similarly, for dropping indexes you can use if_exists argument as:

    op.drop_index('ix_some_index', if_exists=True)