How to use Postgresql enum type via Kotlin Exposed ORM?

Question

I have added some Postgresql types to Exposed as extensions. It has two ready types named enumeration and enumerationByName. I tested both of them with no success for mapping a postgre enum type to Kotlin enum class. In both reading and writing it raises error

enum class TicketStatus(val status: String) {
    Open("open"),
    Close("close"),
    InProgress("in_progress")
}

class Ticket(id: EntityID<UUID>) : Entity<UUID>(id) {
    companion object : EntityClass<UUID, Ticket>(Tickets)

    var geom by Tickets.geom
    var description by Tickets.description
    var status by Tickets.status
    var createdAt by Tickets.createdAt
    var updatedAt by Tickets.updatedAt
    var owner by Tickets.owner
}

When reading:

java.lang.IllegalStateException: open is not valid for enum TicketStatus

Phyto

Maybe it's case sensitive? Try to either rename the elements to open, close etc. or read some upper-cased names from the DB. – Traceytrachea

@Traceytrachea Before the question it was so! Does not work! – Phyto

Where is your "Tickets" object? How do you declare status? – Debose

Answer 1

You should declare status column as follow:

object Tickets: Table() {
   val status = enumeration("status", TicketStatus::class.java) // will create integer column
   val status = enumerationByName("status", TicketStatus::class.java) // will create varchar with TicketStatus names
}

In the new version:

object Tickets: Table() {
   val status = enumeration("status", TicketStatus::class) // will create integer column
   val status = enumerationByName("status", 10, TicketStatus::class) // will create varchar with TicketStatus names
}

Plausible

fails with "... of org.postgresql.util.PGobject is not valid for enum ..." for me – Toed

@Plausible it seems with newer version of Expose, you should use just TicketStatus::class , at least for me adding .java did not work. – Pinckney

Answer 2

I realize this is an old question, but if anyone is still looking for an answer here you go:

Exposed has a customEnumeration that can be used to deal with Enums, and is particularly useful if you are using Strings, or another non-default enumerator to back your enum.

For postgres, you'll first need to define a class like so:

class PGEnum<T:Enum<T>>(enumTypeName: String, enumValue: T?) : PGobject() {
    init {
        value = enumValue?.name
        type = enumTypeName
    }
}

Then in your table definition, use the following to define your column, replacing the placeholders as necessary:

val enumColumn = customEnumeration(
            "ENUM_COLUMN",
            "ENUM_SCHEMA.ENUM_TYPE",
            { value ->
                when (value) {
                    is PGobject -> LocalEnumClass.valueOf(value.value)
                    is String -> LocalEnumClass.valueOf(value)
                    else -> error("Can't convert ENUM_COLUMN")
                }
            },
            { PGEnum("ENUM_SCHEMA.ENUM_TYPE", it) }
)

I was running into the same org.postgresql.util.PGobject is not valid for enum before I did this.

See here for more information, and for non-Postgres databases

Symploce

Answer 3

While other answers are useful, no one shown a way to automatically create and update enums based on a Kotlin enum. The enum will be stored in PostgreSQL as the name of the class, but in lowercase. Values will have the same name as their Java/Kotlin counterparts.

/**
 * Creates and updates a PostgreSQL enum based on a Kotlin enum
 *
 * **This does not remove values from the PostgreSQL enum, and it does not insert new enums based on order!**
 *
 * @param enumValues a callback that provides a list of the valid enum values
 */
inline fun <reified T : Enum<T>> Transaction.createOrUpdatePostgreSQLEnum(enumValues: Array<T>) {
    val valueNames = enumValues.map { it.name }
    val clazzName = T::class.simpleName!!
    val psqlType = clazzName.lowercase()
    val joined = valueNames.joinToString { "'$it'" }

    val alreadyInsertedEnumValues = mutableSetOf<String>()

    exec("SELECT  n.nspname AS enum_schema,  \n" +
            "        t.typname AS enum_name,  \n" +
            "        e.enumlabel AS enum_value\n" +
            "FROM    pg_type t JOIN \n" +
            "        pg_enum e ON t.oid = e.enumtypid JOIN \n" +
            "        pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n" +
            "WHERE   t.typname = '$psqlType'") {
        while (it.next())
            alreadyInsertedEnumValues.add(it.getString("enum_value"))
    }

    val missingEnums = valueNames.filter { it !in alreadyInsertedEnumValues }

    if (alreadyInsertedEnumValues.isEmpty()) {
        exec("CREATE TYPE ${clazzName.lowercase()} AS ENUM ($joined);")
    } else if (missingEnums.isNotEmpty()) {
        for (missingEnum in missingEnums) {
            exec("ALTER TYPE ${clazzName.lowercase()} ADD VALUE '$missingEnum';")
        }
    }
}

inline fun <reified T : Enum<T>> Table.postgresEnumeration(
    columnName: String
) = customEnumeration(columnName, T::class.simpleName!!.lowercase(),
    { value -> enumValueOf<T>(value as String) }, { PGEnum(T::class.simpleName!!.lowercase(), it) })

// From https://github.com/JetBrains/Exposed/wiki/DataTypes
class PGEnum<T : Enum<T>>(enumTypeName: String, enumValue: T?) : PGobject() {
    init {
        value = enumValue?.name
        type = enumTypeName
    }
}

You can use it in your table like this:

object EnumExample : LongIdTable() {
    val type = postgresEnumeration<AchievementType>("type")
}

Keep in mind that you need to create the enum BEFORE creating the table that uses the enum!

transaction {
    createOrUpdatePostgreSQLEnum(AchievementType.values())

    SchemaUtils.createMissingTablesAndColumns(
        EnumTable
    )
}

I've also published a library with this code: https://github.com/PerfectDreams/ExposedPowerUtils

Daglock

Answer 4

I created this extension function to work with postgres enums:

inline fun <reified T : Enum<T>> Table.postgresEnumeration(
   columnName: String,
   postgresEnumName: String
) = customEnumeration(columnName, postgresEnumName,
    { value -> enumValueOf<T>(value as String) }, { PGEnum(postgresEnumName, it) })

Then you can use like this:

 val theEnumColum = postgresEnumeration<YourEnumClass>("your_cloumn_name",
 "your_postgres_enum_name")

Mekong

Answer 5

Use without parameters, postgres and H2 database

val db = Database.connect(.....)

val isPg get() = db.dialect.name == "postgresql"
val isH2 get() = db.dialect.name == "h2"

inline fun <reified T : Enum<T>> Table.customEnum(): Column<T> {
    val columnName = T::class.simpleName?.lowercase()!!
    val sqlPg = "${T::class.simpleName?.lowercase()}_type"
    val sqlH2 = "ENUM(${enumValues<T>().joinToString { "'${it.name}'" }})"
    val fromDb: (Any) -> T = { enumValueOf(it as String) }
    return when {
        isPg -> customEnumeration(columnName, sqlPg, fromDb) { PGEnum(sqlPg, it) }
        isH2 -> customEnumeration(columnName, sqlH2, fromDb) { it.name }
        else -> throw IllegalArgumentException("Unknown db")
    }
}

Use

val status = customEnum<Status>()

or

val status:Column<Status> = customEnum()

Add enum type in postgres

class BuilderEnum {
    private val list = mutableListOf<String>()
    inline fun <reified T : Enum<T>> addEnumType() = addEnumType(T::class)
    fun <T : Enum<T>> addEnumType(c: KClass<T>) = apply {list.add(addEnumTypeExecSql(c)) }
    fun build() = transaction { list.forEach { exec(it) } }
}

fun <T : Enum<T>> addEnumTypeExecSql(enum: KClass<T>): String {
    val enumTypeName = "${enum.simpleName?.lowercase()}_enum_type"
    return """
        DO $$
        BEGIN
        IF NOT EXISTS (SELECT * FROM pg_type WHERE typname = '$enumTypeName') THEN
          CREATE TYPE $enumTypeName AS ENUM (${enum.java.enumConstants.joinToString { "'${it}'" }});
        END IF;
        END $$;
    """.trimIndent()
}

use

BuilderEnum()
    .addEnumType<Role>()
    .addEnumType<Status>()
    .build()

Farrar