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.

Cookgeneral

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

Binns

This questions is a sanity check for myself. Could the index be defined without is_canceled being part of the index definition. From the postgres docs about partial indexes: "enforces uniqueness among the rows that satisfy the index predicate, without constraining those that do not". So, Index('only_one_active_invoice', invoice_id, unique=True, postgresql_where=~is_canceled) where only invoice_id is used in the definition would be enough, because we only want to consider non-cancelled invoices, right? – Baeyer

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.

Logician

Answer 3

To add to the answer by sas, postgresql_where does not seem to be able to accept multiple booleans. So in the situation where you have TWO null-able columns (let's assume an additional 'price' column) it is not possible to have four partial indices for all combinations of NULL/~NULL.

One workaround is to use default values which would never be 'valid' (e.g. -1 for price or '' for a Text column. These would compare correctly, so no more than one row would be allowed to have these default values.

Obviously, you will also need to insert this default value in all existing rows of data (if applicable).

Unavailing