Conditional index in a migration

[demisx]

Is it possible to specify a condition index when creating table in a migration? Something that would result in the following index:

create index category_score_id on products(category, score desc, id desc) where active;

Thank you.

[bendrucker]

No partial index support right now (you need raws)

[demisx]

So looks like I need to write out the whole thing via knex.raw, right?

knex.raw('create index category_score_id on products(category, score desc, id desc) where active');

Or is there a way to offload some of it to the table.index() and then the rest to the knex.raw?

[bendrucker]

Full raw unfortunately

[brandonramirez]

I'm interested in taking a stab at this feature since it seems (at first glance) easy enough and it would benefit my team. I'm not familiar with the knex code base, so I can't commit to getting it done nor commit to a timeframe, but I'll see what I can do.

Not all RDBMS's support partial indexes. I know PostgreSQL does and MySQL does not. My focus is PostgreSQL, but if I can make it re-usable by others, I'll try to do that.

@tgriesser If there are any particular design considerations or approaches you'd like me to take, please let me know. Especially given this feature will not work on all RDBMS's. I figure there is already an established pattern for handling this constraint.

I'll submit a PR once it's complete and the tests pass. I am reviewing the CONTRIBUTING.md file.