Unique Partial Indexes throwing UniqueViolation

[Michael Bukachi]

Hi

I'm trying to setup unique partial indexes so that constraint violation is only thrown when a a certain column is true. This is the index I'm using:

__table_args__ = (
   Index(
     "idx_one_active_fulfillment",
     member_id,
     status,
     unique=True,
     postgres_where=(status == 'ACTIVE'),
   ),
)

As long as the status is not 'ACTIVE', a member can have multiple entries i.e

member_id, status

1, 'COMPLETED'

1, 'COMPLETED'

2, 'ACTIVE'

However, this is not the case. Once I insert an entry e.g 1, 'COMPLETED'; I can't insert it again since it throws an error of 'duplicate key value violates unique constraint'

The weird thing is that, if I run all the queries using psql, it works properly. Is there something I'm missing? I'm trying to see what `create_all` executes but it seems setting echo=True doesn't affect it.

Cheers.

[Michael Bukachi]

Typo alert. It's supposed to be `postgresql_where`. How silly of me.

[Mike Bayer]

not silly at all, it's kind of an unfortunate situation w/ postgresql's naming, the name 'postgres' might be worth hardcoding somehwere as a universal "warn" kind of thing just so it doesn't skip silently

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d76c0079-0fd3-4bdd-ad4a-5e6e45b54f9cn%40googlegroups.com.