Generic partial Index support for sqlalchemy

Question

I realize, that the (above) the consensus is don't test with a db not used in production.

I want to abstract partial index support for sqlalchemy such that I could use either Postgres or Sqlite.

I've seen that with PostgreSQL I can use

    Index('only_one_active_invoice', 
          invoice_id, active,
          unique=True,
          postgresql_where=(active)
    ),

But I see that partial index is also supported in sqlite https://sqlite.org/partialindex.html

Is there some sort of generic partial index support for sqlalchemy with which my module could work for either postgres or sqlite databases?

Andrii Gerasymchuk · Accepted Answer · 2017-07-21 10:19:02Z

1

There is no general way, according to doc

You should use for sqlite

idx = Index('test_idx1', tbl.c.data,
            sqlite_where=and_(tbl.c.data > 5, tbl.c.data < 10))

and for postgres:

idx = Index('my_index', my_table.c.id, 
            postgresql_where=my_table.c.value > 10)

in your case, you can check db engine and initialize index with kwargs

answered Jul 21, 2017 at 10:19

363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1