Multiple conditions in postgresql_where?

Question

postgresql_where is useful to get around the (in my opinion wrong, but apparently the SQL standard defines it) way in which Postgres defines unique-ness, where Null values are always unique. A typical example is shown below, where no item can have identical name+purpose+batch_id values (and None/Null is considered one unique value due to the second Index).

class Item(StoredObject, Base):
    batch_id = Column(Integer, ForeignKey('batch.id'))
    group_id = Column(Integer, ForeignKey('group.id'))
    name = Column(Text, nullable=False)
    purpose = Column(Text, nullable=False, default="")
    __table_args__ = (
        Index('idx_batch_has_value',
              'group_id', 'name', 'purpose', 'batch_id',
              unique=True,
              postgresql_where=(batch_id.isnot(None))),
        Index('idx_batch_has_no_value',
              'group_id', 'name', 'purpose',
              unique=True,
              postgresql_where=(batch_id.is_(None))),
        )

However, I want the same behaviour across two ids (batch_id and group_id), that is to say that no item can have identical name+purpose+batch_id+group_id values (None/Null is considered one unique value in both batch_id and group_id).

I can workaround this by creating a 'default' batch/group object with a fixed ID (say 0). This means I'd have to ensure that batch/group object exists, cannot be deleted, and that that id doesn't get re-appropriated for another 'real' batch/group objects (not to mention I'd have to remember to reduce all counts by one when using/writing functions which count how many batches/groups I have).

Do-able, and I'm about to do it now, but there must be a better way! Is there something like:-

postgresql_where = (batch_id.isnot(None) AND group_id.isnot(None))

That would solve the problem where, in my opinion, it is meant to be solved, in the DB rather than in my model and/or initialization code.

[`sqlalchemy.sql.expression.and_()`](http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.and_), relevant part from the tutorial: http://docs.sqlalchemy.org/en/latest/core/tutorial.html#conjunctions. — Ilja Everilä, Apr 20 '18 at 06:17
Related: [Using OR in SQLAlchemy](https://stackoverflow.com/questions/7942547/using-or-in-sqlalchemy), [Syntax for AND and OR statements in SQLalchemy core?](https://stackoverflow.com/questions/22124159/syntax-for-and-and-or-statements-in-sqlalchemy-core) — Ilja Everilä, Apr 20 '18 at 06:27
@IljaEverilä amazing, I knew that syntax but didn't know it was applicable here. Thanks, would you like to write up your comments as an answer? — Ng Oon-Ee, Apr 20 '18 at 07:11

score -2 · Answer 1 · answered May 30 '23 at 07:05

-2

from sqlalchemy import and_

postgresql_where=and_(batch_id.isnot(None), group_id.isnot(None))

answered May 30 '23 at 07:05

flomaster

1,563
16
16

Multiple conditions in postgresql_where?

1 Answers1