CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Brian Bruggeman
Brian Bruggeman

Reputation: 5314

SQLAlchemy: Declarative ORM - build index using GIST and TSRANGE

I'd like to build an index that essentially looks like this in sql:

CREATE INDEX IF NOT EXISTS new_index ON schema.tablename USING gist (tsrange(start, "end"))

Where my declarative ORM model looks something like this:

import sqlalchemy as sa

class Tablename(Mixins):

    __table_args__ = (
        sa.Index('index_name', postgresql_using="gist"),  # ????
        {'schema': 'schema'}
        )

    start = sa.Column(pg.TIMESTAMP, autoincrement=False, primary_key=True)
    end = sa.Column(pg.TIMESTAMP, nullable=False)

And later, I'd like to use alembic which should include a downgrade such as:

 op.drop_index('index', 'tablename', schema='schema')

Which effectively has the following SQL:

 DROP INDEX IF EXISTS schema.index

Upvotes: 3

Views: 3035

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

SQLAlchemy's Index supports passing SQL function expressions, given that the backend supports functional indexes:

import sqlalchemy as sa

class Tablename(Mixins):

    start = sa.Column(pg.TIMESTAMP, autoincrement=False, primary_key=True)
    end = sa.Column(pg.TIMESTAMP, nullable=False)

    __table_args__ = (
        sa.Index('index_name', sa.func.tsrange(start, end),
                 postgresql_using="gist"),
        {'schema': 'schema'}
        )

Note that if you move the __table_args__ definition after the column attribute definitions, you can use them in your SQL function expression.

Upvotes: 9

Related Questions