Get conditional indexes with scala using slick

Question

So I was trying to implement an index from my database to my slick tables. I'm using postgres as my database and this is how my index looks:

"index_unq_user_status" UNIQUE, btree (user_id) WHERE status::text = 'Pending'::text

Basically, it can't be two rows with the same user_id if they have both a 'Pending' status.

What's my problem? When I generate my tables with Slick I get this:

val index1 = index("index_unq_user_status", userId, unique=true)

It means, just can be one row with one userId.

I want to make an integration test using an h2 database and it uses the tables from slick and it creates the wrong index.

Is there any way to achieve that conditional index in slick?

Answer 1

There's no built-in or straightforward support for partial indexes with Sick.

However, as your index1 is creating SQL DDL statements to change the schema (if you apply them), what you could do is: remove index1, and instead after you apply the table query .schema.create you can run a plain SQL statement to apply your index.

In outline, that would be something like:

val addIndex = sqlu""" ALTER TABLE table ADD INDEX ... """
val createAction = (MyTable.schema.create andThen addIndex)

I'm not sure how you're structuring your H2 integration tests, but you could conditionally omit the PG-specific addIndex in those tests.