Custom data types

If a database-specific data type is not supported by Exposed, you can extend any existing and open column type or implement a custom ColumnType class to achieve the same functionality.

The following examples describe different ways to customize a column type, register a column with the custom type, and then start using it in transactions.

Hierarchical tree-like data

PostgreSQL provides a data type, ltree, to represent hierarchical tree-like data.

The hierarchy labels are stored as strings, so the existing StringColumnType class can be extended with a few overrides:

            import org.postgresql.util.PGobject

            class LTreeColumnType : StringColumnType() {
                override fun sqlType(): String = "LTREE"

                override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
                    val parameterValue: PGobject? = value?.let {
                        PGobject().apply {
                            type = sqlType()
                            this.value = value as? String
                        }
                    }
                    super.setParameter(stmt, index, parameterValue)
                }
            }
        

A table extension function can then be added to register a new column with this type:

            fun Table.ltree(name: String): Column<String> = registerColumn(name, LTreeColumnType())

            object TestTable : Table("test_table") {
                val path = ltree("path")

                init {
                    index(customIndexName = "path_gist_idx", indexType = "GIST", columns = arrayOf(path))
                    index(customIndexName = "path_idx", indexType = "BTREE", columns = arrayOf(path))
                }
            }
        

String values representing hierarchy labels can then be inserted and queried from the path column. The following block shows an update of all records that have a stored path either equal to or a descendant of the path Top.Science, by setting a subpath of the first 2 labels as the updated value:

            transaction {
                TestTable.update(
                    where = { TestTable.path isDescendantOrEq "Top.Science" }
                ) {
                    it[path] = path.subltree(0, 2)
                }
            }

            fun <T : String?> Expression<T>.subltree(start: Int, end: Int) =
                CustomStringFunction("SUBLTREE", this, intParam(start), intParam(end))

            infix fun <T : String?> ExpressionWithColumnType<T>.isDescendantOrEq(other: T) =
                IsDescendantOrEqOp(this, wrap(other))

            class IsDescendantOrEqOp<T : String?>(
                left: Expression<T>,
                right: Expression<T>
            ) : ComparisonOp(left, right, "<@")
        

Date and time data

MySQL and MariaDB provide a data type, YEAR, for 1-byte storage of year values in the range of 1901 to 2155.

This example assumes that the column accepts string input values, but a numerical format is also possible, in which case IntegerColumnType could be extended instead:

            class YearColumnType : StringColumnType(), IDateColumnType {
                override fun sqlType(): String = "YEAR"

                override val hasTimePart: Boolean = false

                override fun valueFromDB(value: Any): String = when (value) {
                    is java.sql.Date -> value.toString().substringBefore('-')
                    else -> error("Retrieved unexpected value of type ${value::class.simpleName}")
                }
            }

            fun Table.year(name: String): Column<String> = registerColumn(name, YearColumnType())
        

The IDateColumnType interface is implemented to ensure that any default expressions are handled appropriately. For example, a new object CurrentYear can be added as a default to avoid issues with the strict column typing:

            object CurrentYear : Function<String>(YearColumnType()) {
                override fun toQueryBuilder(queryBuilder: QueryBuilder) {
                    queryBuilder { +"CURRENT_DATE" }
                }
            }

            object TestTable : Table("test_table") {
                val established = year("established").defaultExpression(CurrentYear)
            }
        

String values of different formats (depending on the enabled sql_mode) can then be inserted and queried from the year column:

            transaction {
                // disable strict mode to allow truncation of full date strings
                exec("SET sql_mode=''")

                val yearData = listOf("1901", "2000", "2023-08-22", "2155")
                TestTable.batchInsert(yearData) { year ->
                    this[TestTable.established] = year
                }

                TestTable
                    .selectAll()
                    .where { TestTable.established less CurrentYear }
                    .toList()
            }
        

Ranges of data

PostgreSQL provides multiple range data types of different subtypes.

If more than one range subtype needs to be used, a base RangeColumnType class could be first introduced with the minimum common logic:

                    import org.postgresql.util.PGobject

                    abstract class RangeColumnType<T : Comparable<T>, R : ClosedRange<T>>(
                        val subType: ColumnType<T>,
                    ) : ColumnType<R>() {
                        abstract fun List<String>.toRange(): R

                        override fun nonNullValueToString(value: R): String {
                            return "[${value.start},${value.endInclusive}]"
                        }

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

                        override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
                            val parameterValue: PGobject? = value?.let {
                                PGobject().apply {
                                    type = sqlType()
                                    this.value = nonNullValueToString(it as R)
                                }
                            }
                            super.setParameter(stmt, index, parameterValue)
                        }

                        override fun valueFromDB(value: Any): R? = when (value) {
                            is PGobject -> value.value?.let {
                                val components = it.trim('[', ')').split(',')
                                components.toRange()
                            }
                            else -> error("Retrieved unexpected value of type ${value::class.simpleName}")
                        }
                    }
                
               

                    abstract class RangeR2dbcColumnType<T : Comparable<T>, R : ClosedRange<T>>(
                        val subType: ColumnType<T>,
                    ) : ColumnType<R>() {
                        abstract fun List<String>.toRange(): R

                        override fun nonNullValueToString(value: R): String =
                            toPostgresqlValue(value)

                        override fun nonNullValueAsDefaultString(value: R): String =
                            "'${nonNullValueToString(value)}'"

                        override fun valueFromDB(value: Any): R = when (value) {
                            is String -> value.trim('[', ')').split(',').toRange()
                            else -> error("Unexpected DB value type: ${value::class.simpleName}")
                        }

                        companion object {
                            fun <T : Comparable<T>, R : ClosedRange<T>> toPostgresqlValue(range: R): String =
                                "[${range.start},${range.endInclusive}]"
                        }
                    }
                
                

A class for the type int4range that accepts IntRange values could then be implemented:

                    class IntRangeColumnType : RangeColumnType<Int, IntRange>(IntegerColumnType()) {
                        override fun sqlType(): String = "INT4RANGE"

                        override fun List<String>.toRange(): IntRange {
                            return IntRange(first().toInt(), last().toInt() - 1)
                        }
                    }
                    
                

                    class IntRangeColumnType : RangeR2dbcColumnType<Int, IntRange>(IntegerColumnType()) {
                        override fun sqlType(): String = "INT4RANGE"

                        override fun List<String>.toRange(): IntRange {
                            return IntRange(first().toInt(), last().toInt() - 1)
                        }
                    }
                    
                

            fun Table.intRange(name: String): Column<IntRange> = registerColumn(name, IntRangeColumnType())
            
        

If a custom Kotlin implementation for a DateRange is set up (using Iterable and ClosedRange), then a class for the type daterange can also be added. This implementation would require a dependency on exposed-kotlin-datetime:

                    class DateRangeColumnType : RangeColumnType<LocalDate, DateRange>(KotlinLocalDateColumnType()) {
                        override fun sqlType(): String = "DATERANGE"

                        override fun List<String>.toRange(): DateRange {
                            val endInclusive = LocalDate.parse(last()).minus(1, DateTimeUnit.DAY)
                            return DateRange(LocalDate.parse(first()), endInclusive)
                        }
                    }
                    
                

                    class DateRangeColumnType : RangeR2dbcColumnType<LocalDate, DateRange>(KotlinLocalDateColumnType()) {
                        override fun sqlType(): String = "DATERANGE"

                        override fun List<String>.toRange(): DateRange {
                            val endInclusive = LocalDate.parse(last()).minus(1, DateTimeUnit.DAY)
                            return DateRange(LocalDate.parse(first()), endInclusive)
                        }
                    }
                    
                

            fun Table.dateRange(name: String): Column<DateRange> = registerColumn(name, DateRangeColumnType())
            
        

Using the column in tables

These new column types can be used in a table definition:

            object TestTable : Table("test_table") {
                val amounts = intRange("amounts").default(1..10)
                val holidays = dateRange("holidays")
            }
            

Querying and operators

With the addition of some custom functions, the stored data can then be queried to return the upper bound of the date range for all records that have an integer range within the specified bounds:

            transaction {
                val holidayEnd = TestTable.holidays.upperBound()
                TestTable
                    .select(holidayEnd)
                    .where { TestTable.amounts isContainedBy 0..100 }
                    .toList()
            }

            fun <T : Comparable<T>, CR : ClosedRange<T>, R : CR?> ExpressionWithColumnType<R>.upperBound() =
                CustomFunction("UPPER", (columnType as RangeColumnType<T, CR>).subType, this)

            infix fun <R : ClosedRange<*>?> ExpressionWithColumnType<R>.isContainedBy(other: R) =
                RangeIsContainedOp(this, wrap(other))

            class RangeIsContainedOp<R : ClosedRange<*>?>(
                left: Expression<R>,
                right: Expression<R>
            ) : ComparisonOp(left, right, "<@")
        

Predefined string data

MySQL and MariaDB provide a data type, SET, for strings that can have zero or more values from a defined list of permitted values. This could be useful, for example, when storing a list of Kotlin enum constants.

To use this type, a new ColumnType could be implemented with all the necessary overrides. This example instead takes advantage of the existing logic in StringColumnType as the base for database storage, then uses a custom ColumnTransformer to achieve the final transformation between a set of enum constants and a string:

            class SetColumnType<T : Enum<T>>(
                private val enumClass: KClass<T>
            ) : StringColumnType() {
                // uses reflection to retrieve elements of the enum class
                private val enumConstants by lazy {
                    enumClass.java.enumConstants?.map { it.name } ?: emptyList()
                }

                override fun sqlType(): String = enumConstants
                    .takeUnless { it.isEmpty() }
                    ?.let { "SET(${it.joinToString { e -> "'$e'" }})" }
                    ?: error("SET column must be defined with a list of permitted values")
            }

            inline fun <reified T : Enum<T>> Table.set(name: String): Column<String> =
                registerColumn(name, SetColumnType(T::class))

            class EnumListColumnType<T : Enum<T>>(
                private val enumClass: KClass<T>
            ) : ColumnTransformer<String, List<T>> {
                private val enumConstants by lazy {
                    enumClass.java.enumConstants?.associateBy { it.name } ?: emptyMap()
                }

                override fun unwrap(value: List<T>): String {
                    return value.joinToString(separator = ",") { it.name }
                }

                override fun wrap(value: String): List<T> = value
                    .takeUnless { it.isEmpty() }?.let {
                        it.split(',').map { e ->
                            enumConstants[e]
                                ?: error("$it can't be associated with any value from ${enumClass.qualifiedName}")
                        }
                    }
                    ?: emptyList()
            }
        

The new column type and transformer can then be used in a table definition:

            enum class Vowel { A, E, I, O, U }

            object TestTable : Table("test_table") {
                val vowel: Column<List<Vowel>> = set<Vowel>("vowel")
                    .transform(EnumListColumnType(Vowel::class))
                    .default(listOf(Vowel.A, Vowel.E))
            }
        

Lists of enum constants can then be inserted and queried from the set column. The following block shows a query for all records that have Vowel.O stored at any position in the set column string:

            transaction {
                TestTable.insert { it[vowel] = listOf(Vowel.U, Vowel.E) }
                TestTable.insert { it[vowel] = emptyList() }
                TestTable.insert { it[vowel] = Vowel.entries }

                TestTable
                    .selectAll()
                    .where { TestTable.vowel.findInSet(Vowel.O) greater 0 }
                    .toList()
            }

            fun <T : Enum<T>> Expression<List<T>>.findInSet(enum: T) =
                CustomFunction("FIND_IN_SET", IntegerColumnType(), stringParam(enum.name), this)
        

Key-Value pair data

PostgreSQL provides a data type, hstore, to store key-value data pairs in a single text string.

The existing StringColumnType class can be extended with a few overrides:

            import org.postgresql.util.PGobject

            class HStoreColumnType : TextColumnType() {
                override fun sqlType(): String = "HSTORE"

                override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
                    val parameterValue: PGobject? = value?.let {
                        PGobject().apply {
                            type = sqlType()
                            this.value = value as? String
                        }
                    }
                    super.setParameter(stmt, index, parameterValue)
                }
            }
        

A table extension function can then be added to register a new column with this type. This example assumes that the input values will be of type Map<String, String>, so transform() is used on the string column to handle parsing:

            fun Table.hstore(name: String): Column<String> = registerColumn(name, HStoreColumnType())

            object TestTable : Table("test_table") {
                val bookDetails = hstore("book_details").transform(
                    wrap = {
                        it.trim('{', '}').split(", ")
                            .associate { pair ->
                                pair.substringBefore("=") to pair.substringAfter("=")
                            }
                    },
                    unwrap = {
                        it.entries.joinToString(separator = ",") { (k, v) ->
                            "\"$k\"=>\"$v\""
                        }
                    }
                )
            }
        

Map values representing key-value pairs of strings can then be inserted and queried from the bookDetails column. The following block queries the value associated with the title key from all bookDetails records:

            transaction {
                TestTable.insert {
                    it[bookDetails] = mapOf(
                        "title" to "Kotlin in Action",
                        "edition" to "2"
                    )
                }

                val bookTitle = TestTable.bookDetails.getValue("title")
                TestTable
                    .select(bookTitle)
                    .toList()
            }

            fun <T : Map<String, String>> Expression<T>.getValue(key: String) =
                CustomOperator("->", TextColumnType(), this, stringParam(key))
        

Case insensitive data

PostgreSQL provides a data type, citext, that represents a case-insensitive string type.

To support citext using JDBC, extend VarCharColumnType and override .sqlType() and .setParameter(). For R2DBC, you need to extend the same base class but skip PGobject:

                    import org.postgresql.util.PGobject

                    class CitextColumnType(
                        colLength: Int
                    ) : VarCharColumnType(colLength) {
                        override fun sqlType(): String = "CITEXT"

                        override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
                            val parameterValue: PGobject? = value?.let {
                                PGobject().apply {
                                    type = sqlType()
                                    this.value = value as? String
                                }
                            }
                            super.setParameter(stmt, index, parameterValue)
                        }
                    }
                

                    class CitextR2dbcColumnType(
                        colLength: Int
                    ) : VarCharColumnType(colLength) {
                        override fun sqlType(): String = "CITEXT"
                    }
                

                    fun Table.citext(name: String, length: Int): Column<String> =
                        registerColumn(name, CitextColumnType(length))
                

                    fun Table.citext(name: String, length: Int): Column<String> =
                        registerColumn(name, CitextR2dbcColumnType(length))

Using the column in tables

A table extension function can then be added to register a new column with this type:

            object TestTable : Table("test_table") {
                val firstName = citext("first_name", 32)
            }
            

To use the citext data type, the extension must first be enabled in the database. You can enable it by running the following SQL query:

CREATE EXTENSION IF NOT EXISTS citext;

Querying with case-insensitive matching

You can then insert and query string values from the firstName column in a case-insensitive manner:

            transaction {
                val allNames = listOf("Anna", "Anya", "Agna")
                TestTable.batchInsert(allNames) { name ->
                    this[TestTable.firstName] = name
                }

                TestTable
                    .selectAll()
                    .where { TestTable.firstName like "an%" }
                    .toList()
            }
            

Last modified: 27 November 2025