Does anyone know how to define a multi-column inde...

# exposed

[Richie Bresnan]

Does anyone know how to define a multi-column index in Exposed, where one the columns is nullable, and the nulls are not treated as distinct.

This equivalent in PG SQL:

create unique index on mytable(col, nullableCol) nulls not distinct;

[Chantal Loncle]

Hi @Richie Bresnan While some improvements have been made to the Index data class, it is still not fully open for such customization across all supported databases, so adding this clause is not immediately possible. But it is technically possible, with a bit of a hack.

Every open property and method in a VendorDialect, including createIndex(), can actually be overriden at a lower level when the database connection is defined.
For your specific use case, if it's just inserting a string in the right position, you could set up the NULLS NOT DISTINCT clause as an "operator", provide it to the filterCondition parameter, and then override the resulting SQL if this "operator" is used.

The caveat being that, if you want to use both this clause and an actual index filter condition, you'll need to include some logic to move the expected WHERE keyword to the appropriate position instead of just replacing it.

Here's an example that generates the sql you mentioned:

class NullsNotDistinct : Op<Boolean>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        queryBuilder.append("NULLS NOT DISTINCT")
    }
}

object MyTable : Table("mytable") {
    val col = integer("col")
    val nullableCol = integer("nullableCol").nullable()

    init {
        index(columns = arrayOf(col, nullableCol), isUnique = true) { NullsNotDistinct() }
    }
}

// override creation logic in DatabaseConfig.explicitDialect
val db = Database.connect(
    url = "",
    // ...,
    databaseConfig = DatabaseConfig {
        explicitDialect = object : PostgreSQLDialect() {
            override fun createIndex(index: Index): String {
                val original = super.createIndex(index)
                return original.replace(" WHERE NULLS NOT DISTINCT", " NULLS NOT DISTINCT")
            }
        }
    }
)

An alternative could be to set up the clause as a "function" instead and provide it as an argument to the functions parameter, forcing it to appear after the index column set, but within the parentheses: mytable (col_1 INT NOT NULL, col_2 INT NULL, NULLS NOT DISTINCT). Then use the same override to move the clause if present to outside the parentheses.
Or completely duplicate and rewrite the existing logic to account for your use case.

[Richie Bresnan]

wow, super detailed and helpful answer. thank you Chantal!