Defining and using PostgreSQL's ts_vector column type in Ktor using Exposed

Question

I have created the ts_vector columns in the relevant tables in my PostgreSQL database, and I now need to add the respective column types in the Exposed tables & entities so that I can perform FTS through my endpoint.

I have not managed to find any documentation on how to do this, here is what i've tried so far:

private const val TS_VECTOR_SQL_TYPE = "tsvector"

class TsVectorColumnType : ColumnType<String>() {
    override fun sqlType(): String = TS_VECTOR_SQL_TYPE

    override fun valueFromDB(value: Any): String? {
        return value as String
    }

    override fun valueToDB(value: String?): Any? {
        return PGobject().apply {
            type = TS_VECTOR_SQL_TYPE
            this.value = value
        }.value
    }


    override fun notNullValueToDB(value: String): Any {
        return PGobject().apply {
            type = TS_VECTOR_SQL_TYPE
            this.value = value
        }
    }

    override fun nonNullValueToString(value: String): String {
        return "'$value'"
    }
}

Tried to create a custom column type for it and then use it like so in the table definitions val tsvectorContent = TsVectorColumnType().

Is this approach correct? If it is, I'm having trouble understanding how the logic would work for using it to search based on the user's query.

Answer 1

I managed to get this working thankfully by creating a custom column type for the ts_vector column and the respective tables holding that content along with the relevant item ID.

I also created some custom operators and expressions in exposed in order to keep using its DSL. Furthermore, I am not aware if it is acceptable to post my own article on this, so if it is not, let me know and I will remove it.

You can find the whole flow I followed on said article here on my substack.

In case you'd rather get only the relevant parts to this question, here you go:

private const val TS_VECTOR_SQL_TYPE = "tsvector"

class TsVectorColumnType : ColumnType<String>(nullable = true) {
    override fun sqlType(): String = TS_VECTOR_SQL_TYPE

    override fun valueFromDB(value: Any): String? {
        return value as String
    }

    override fun valueToDB(value: String?): Any? {
        return PGobject().apply {
            type = TS_VECTOR_SQL_TYPE
            this.value = value
        }.value
    }


    override fun notNullValueToDB(value: String): Any {
        return PGobject().apply {
            type = TS_VECTOR_SQL_TYPE
            this.value = value
        }
    }

    override fun nonNullValueToString(value: String): String {
        return "'$value'"
    }
}

and then register using:

object ResearchLabsTsVectorTable : IntIdTable("labs_tsvector") {
    val researchLab = reference("research_lab_id", ResearchLabTable, onDelete = ReferenceOption.CASCADE)
    val tsvectorContent = registerColumn<String>("tsvector_content", TsVectorColumnType()).databaseGenerated()
}