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.

score 66 · Accepted Answer · answered Jan 16 '15 at 07:37

66

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

answered Jan 16 '15 at 07:37

van

74,297
13
168
171

5

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](https://www.postgresql.org/docs/8/indexes-partial.html#INDEXES-PARTIAL-EX3): **"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? – Obeyed Oct 23 '19 at 08:33

score 16 · Answer 2 · answered Oct 17 '17 at 07:49

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.

score 1 · Answer 3 · answered Apr 02 '18 at 08:23

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

Creating partial unique index with sqlalchemy on Postgres

3 Answers3

Linked

Related