How to use Ktorm to perform WHERE clause operation on custom Postgres “object type” avoiding “PSQLException: ERROR: operator does not exist”

[DaddyMoe]

Whilst performing a WHERE clause operation on a custom Postgres "object type" I ended up the following PSQLException.

Exception

org.postgresql.util.PSQLException: ERROR: operator does not exist: rate = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I have followed the Ktorm official guides here but, there is no mention of custom Postgres types. Any pointers to where I'm being wrong would be highly appreciated. See code below to reproduce:

Thank you.

1. Example test that would produce the above exception

internal class SuppliersInstanceDAOTest {


    @Test
    fun shouldReturnInstanceSequence() {

        val database = Database.connect("jdbc:postgresql://localhost:5432/mydb", user = "postgres", password = "superpassword")

        val instanceDate: LocalDate = LocalDate.of(2019, 4, 1)
        database.withSchemaTransaction("suppliers") {
            database.from(SuppliersInstanceTable)
                .select(SuppliersInstanceTable.instanceSeq)
                .whereWithConditions {
                // The following line causes "ERROR: operator does not exist: rate = character varying"
                    it += SuppliersInstanceTable.rate eq Rate.DAILY
                }.asIterable()
                .first()
                .getInt(1)
        }
        
    }
}

1. Schema

-- Note the special custom enum object type here that I cannot do anything about
CREATE TYPE suppliers.rate AS ENUM
    ('Daily', 'Byweekly');

CREATE TABLE suppliers.instance
(
    rate suppliers.rate NOT NULL,
    instance_value integer NOT NULL
)

TABLESPACE pg_default;

3. Kotlin's Ktorms Entities and bindings

enum class Rate(val value: String) {
    DAILY("Daily"),
    BIWEEKLY("Byweekly")
}

interface SuppliersInstance : Entity<SuppliersInstance> {
    companion object : Entity.Factory<SuppliersInstance>()
    val rate: Rate
    val instanceSeq: Int
}

object SuppliersInstanceTable : Table<SuppliersInstance>("instance") {
    val rate = enum("rate", typeRef<Rate>()).primaryKey().bindTo { it.rate } // <-- Suspect
    //val rate = enum<Rate>("rate", typeRef()).primaryKey().bindTo { it.rate } // Failed too

    val instanceSeq = int("instance_value").primaryKey().bindTo { it.instanceSeq }
}

[DaddyMoe]

Converted to an issue. Please close/delete this

[vincentlauvlwj]

The default enum function in the core module just converts enum values as strings. You should use pgEnum instead. https://github.com/kotlin-orm/ktorm/blob/master/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/SqlTypes.kt#L84-L110

object SuppliersInstanceTable : Table<SuppliersInstance>("instance") {
    val rate = pgEnum<Rate>("rate").primaryKey().bindTo { it.rate }
    val instanceSeq = int("instance_value").primaryKey().bindTo { it.instanceSeq }
}

[DaddyMoe]

Hey Vincent,

Thanks for taking the time to answer. I cannot find a pgEnum function with my current ktorm version as it is not available, I am on ktorm v3.0.0, is there any other workaround or upgrading is the only route to resolve this issue?

Meanwhile looking at the Ktorm repo here for SqlTypes.kt, it looks like pgEnum function is only in ktorm v3.2.x +. However, the latest version available in mavenCentral and jcenter maven repositories is v3.1.0.

Update: Alright! I see there has been a group name change from me.liuwj.ktorm to org.ktorm which has the latest version .

[vincentlauvlwj]

Yes, the group name was changed. If you cannot upgrade to the latest version, simple coping the pgEnum function to your project can also solve the issue.

[DaddyMoe]

Superb. Thank you @vincentlauvlwj!