Support partial unique constrains on PostgreSQL
This is already supported for normal indexes, but if I try to pass the postgresql_where
keyword arg to UniqueConstraint
, it fails with the following error:
sqlalchemy.exc.ArgumentError: Argument 'postgresql_where' is not accepted by dialect 'postgresql' on behalf of <class 'sqlalchemy.sql.schema.UniqueConstraint'>
Comments (8)
-
repo owner -
repo owner - changed component to postgres
-
assigned issue to
-
repo owner - changed milestone to 0.9.xx
-
repo owner - changed milestone to 1.0.xx
-
@zzzeek I spent some time looking into this this morning, and I think that this is an invalid request. Postgres does not support partial UNIQUE CONSTRAINTs. It does, however, support partial UNIQUE INDEXes - a feature which SQLAlchemy supports correctly today.
@GaretJax I believe the functionality you're looking for can be found by changing UniqueConstraint to Index, and simply adding a unique=True. See:
-
repo owner - changed status to invalid
seems like PG doesn't support this on UNIQUE CONSTRAINT, just use a unique index. thanks @fuhrysteve !
-
reporter @fuhrysteve thanks for the research and the clarification!
-
@GaretJax No worries! I actually came across this myself because I didn't realize the relatively subtle distinction between a CONSTRAINT and a UNIQUE INDEX in postgres.
- Log in to comment
okey doke this would be the "def visit_unique_constraint(self, constraint):" added to sqlalchemy/dialects/postgresql/base.py->PGDDLCompiler, adapted from sqlalchemy/sql/compiler.py->DDLCompiler, the other mechanics of "postgresql_where" would be set up as they are for indexes, e.g. the PGDialect.construct_arguments collection. Tests would be straight compile tests in test/dialect/postgresql/test_compiler.py -> CompilerTest, there are some Index tests there now that can be emulated.
In absence of this feature you can use DDL() of course, otherwise pullreqs via github are preferred in case you have an interest :).