Creating partial unique index with sqlalchemy on Postgres

Question

SQLAlchemy supports creating partial indexes in postgresql.

Is it possible to create a partial unique index through SQLAlchemy?

Imagine a table/model as so:

class ScheduledPayment(Base):      invoice_id = Column(Integer)      is_canceled = Column(Boolean, default=False) 

I'd like a unique index where there can be only one "active" ScheduledPayment for a given invoice.

I can create this manually in postgres:

CREATE UNIQUE INDEX only_one_active_invoice on scheduled_payment       (invoice_id, is_canceled) where not is_canceled; 

I'm wondering how I can add that to my SQLAlchemy model using SQLAlchemy 0.9.

Answer 1

class ScheduledPayment(Base):     id = Column(Integer, primary_key=True)     invoice_id = Column(Integer)     is_canceled = Column(Boolean, default=False)      __table_args__ = (         Index('only_one_active_invoice', invoice_id, is_canceled,               unique=True,               postgresql_where=(~is_canceled)),     ) 

Answer 2

In case someone stops by looking to set up a partial unique constraint with a column that can optionally be NULL, here's how:

__table_args__ = (     db.Index(         'uk_providers_name_category',         'name', 'category',         unique=True,         postgresql_where=(user_id.is_(None))),     db.Index(         'uk_providers_name_category_user_id',         'name', 'category', 'user_id',         unique=True,         postgresql_where=(user_id.isnot(None))), ) 

where user_id is a column that can be NULL and I want a unique constraint enforced across all three columns (name, category, user_id) with NULL just being one of the allowed values for user_id.