How many databases support this? Just Postgres and SQL Server?

How many databases support this? Just Postgres and SQL Server?

Yep, those two and SQLite

It can be easily and safely emulated by transforming the index to a unique one I.e. index on tbl1(col1) where col2 = 1 to unique index on tbl1(col1, case when col2 = 2 then primaryKey else null end) because nulls in unique indexes are ignored by default.

Mmmmm. So look, every database has a bunch of fancy proprietary options for creating indexes. We can't possibly expand the @Index annotation to accommodate all these things individually.

Now, perhaps it could make sense to add something like indexDefinition in analogy to columnDefinition in @Column, so you could write:

@Index(name="orders_unbilled_index ", 
       columnList="order_nr", 
       indexDefinition ="where billed is not true")

To produce this DDL:

create index orders_unbilled_index on orders (order_nr)
       where billed is not true;

And of course you could also use it for lots of other things.

I guess that's kinda intellectually consistent at least.

On the other hand, you could just put that create index statement in a DDL script, and the JPA provider will happily execute the script for you, so the need is not really that acute, IMO.

Dunno.

[Note: I wrote this before seeing @beikov's comment.]

It can be easily and safely emulated by transforming the index to a unique one

Ah, that's interesting.

It can be easily and safely emulated by transforming the index to a unique one I.e. index on tbl1(col1) where col2 = 1 to unique index on tbl1(col1, case when col2 = 2 then primaryKey else null end) because nulls in unique indexes are ignored by default.

Thank you for the suggestion, though still trying to add that option since it seems more like an elegant solution, for the relevant databases, IMO.

Mmmmm. So look, every database has a bunch of fancy proprietary options for creating indexes. We can't possibly expand the @Index annotation to accommodate all these things individually.

Looks awsome.
Would using it in ORM libraries be acceptable?

@carmi2214 We need to pick just one of those alternatives. You can't have both 😅

Of course - excuse me, by "awsome" I meant to respond on the indexDefinition idea 😄

Actually, since the @ForeignKey annotation defines a foreignKeyDefinition member which is supposed to specify the whole DDL definition of the FK, instead of just some text to be appended at the end, we had better stay away from the use of "definition" here.

So perhaps what we should do is call it options:

Index(name = "orders_unbilled_index ", 
      columnList = "order_nr", 
      options = "where billed is not true")

We could even add options members to other annotations like @UniqueConstraint, @ForeignKey, and even @Table and @Column.

I think this would be a quite useful enhancement.

@gavinking Great! So, what's next?

So your nagging worked: #485.

But now lets see what other people think.