Partial Index on Postgres where condition is in separate table

Question

I have table schema as:

table_a:
    id
    active boolean
 
table_b:
    id
    table_a_id ForeignKey

I want to apply a partial unique index on table_b but only if active=True for table_a_id.

Is this feasible in postgres?

Answer 1

Not possible. From the manual:

The expression used in the WHERE clause can refer only to columns of the underlying table, but it can use all columns, not just the ones being indexed.