CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
user956424
user956424

Reputation: 1609

how to define a unique constraint depending on column value in postgresql?

I have a transaction table defined as follows:

mtransactions = Table("mtransactions", metadata,
                    Column("id", Integer, primary_key=True),
                    Column("type", String, default='Reg'),
                    Column("cust_id", Integer, ForeignKey("b_customers.id")),
                    Column("date", Date,
                           default=datetime.datetime.utcnow().date()),
                 UniqueConstraint('cust_id','date', name='cust_date_tran'))

there are only 2 types of transactions. For type='1', I would like to have the unique constraint as above i.e cust_id, date. But for transactions of type '2', there can be more than 1 type of transactions for the same date. How do I define such a constraint? Using only sqlalchemy core 1.0.8, python 2.7, postgresql 9.6

Upvotes: 0

Views: 203

Answers (1)

univerio
univerio

Reputation: 20518

You can have functional unique indices:

Index("ix_transaction_type_1", mtransactions.c.cust_id, mtransactions.c.date, postgresql_where=mtransactions.c.type = "1")

Upvotes: 2

Related Questions