PostgreSQL SQLAlchemy index NULLS NOT DISTINCT [duplicate]

Question

I have a table (TA) on which I want to have a composite unique index on two columns (col1 and col2).

col2 can be NULL.

I know Postgres treats NULLs as distinct.

In SQL I would therefore do:

CREATE UNIQUE INDEX index_name ON TA (col1, col2) NULLS NOT DISTINCT

How can I achieve the same in a SQLAlchemy model?

Something like:

class TherapeuticArea(Base):
    __tablename__ = "TA"
    __table_args__ = (
        sa.Index(
            "index_name",
            "col1",
            "col2",
            unique=True,
            # postgresql_ option here? Which one? Or..?
        ),
    )

Note: so far I have added such SQL statement as custom op.execute() to the migration generated by Alembic and it works fine. But then Alembic keeps removing the index on subsequent migrations. Therefore I'd like to have it as a plain declaration in the model

Specifically, the bottom of this answer. The changelog is here and I think it requires Postgres 15+ — roganjosh
– roganjosh, Commented yesterday
I've added an answer to the proposed duplicate that specifies how to do this as it is no longer necessary to use a custom comilation. — snakecharmerb
– snakecharmerb, Commented yesterday

poisoned_monkey · Accepted Answer · 2025-11-20 17:01:13Z

2

You can use postgresql_nulls_not_distinct=True option for these purposes

answered yesterday

poisoned_monkey

1,7841 gold badge8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pynchia yesterday

thank you. I have searched for such an option but got no results. Even now that I know it exists and what it is called, searching it on SQLAlchemy's website shows a mention in a changelog only. Poor documentation

Collectives™ on Stack Overflow

PostgreSQL SQLAlchemy index NULLS NOT DISTINCT [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related