Question 114336 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to create array of LocalDate in jetbrains Exposed table

I have a Postgres Table that has a column of "date[]" representing various dates that a user has a specific behavior, meaning each cell can have multiple dates (for example {"2022-10-31", "2022-10-10"}).

This is connected to an API built in Ktor using Exposed library. I’m trying to represent this in Ktor.

The object property is

val user_behaviour_dates: List<LocalDate?>?

where LocalDate comes from kotlinx.datetime library, so that it can be serialized.

How can I represent this in the Users Table? I have successfully represented another list property that is:

val equipment: List<String?>?

using this library that supports array for ColumnType as

var equipment = array<String?>("equipment", VarCharColumnType(120)).nullable()

but when I am trying to do this with LocalDate, i get the error that the IDateColumnType (from Exposed library) doesn’t have constructors (since its an interface).

Here is the Table. Any ideas?

object UsersTable: Table() {

val userId : Column<Int> = integer("id").autoIncrement()
override val primaryKey: PrimaryKey = PrimaryKey(userId)
val email = varchar("email", 128).uniqueIndex()
val password = varchar("password", 64)
val username = varchar("username", 120)

var equipment = array<String?>("equipment", VarCharColumnType(120)).nullable()

var user_behaviour_dates = array<LocalDate?>("user_behaviour_dates ", IDateColumnType()).nullable()

}

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I finally solved it like this:

        val user_behaviour_dates = array<Date?>("user_behaviour_dates ", KotlinLocalDateColumnType()).nullable()

    The "Date?" type from (java.sql.Date) is recognized by Postgres table, and instead of using IDateColumnType Exposed interface, I used KotlinLocalDateColumnType() which also can be serialized/deserialized in the form of LocalDate as a field.

    Then when writing a function in the API of updating a User for example, a conversion is needed from Kotlin LocalDate to SQL Date (with an in-between javaLocalDate) so that it can be put in the table.

    override suspend fun updateUserPrefs(
 email: String,
 equipment: List<String?>?,
 userBehaviour: List<LocalDate?>?
) {

 return run {

     // Kotlin LocalDate to SQL Date conversion

     var userBehaviourDatesSqlDate: MutableList<Date?>? = null

     if (userBehaviour != null) {
         for (i in userBehaviour.indices) {
             val localDate = userBehaviour[i]
             val date: Date = Date.valueOf(localDate!!.toJavaLocalDate())
             if (userBehaviourDatesSqlDate == null) userBehaviourDatesSqlDate = mutableListOf()
             userBehaviourDatesSqlDate.add(date)
         }
     }

     // Database update

     DatabaseFactory.dbQuery {
         UsersTable.update({ UsersTable.email eq email }) { user ->

             user[UsersTable.equipment] = equipment?.toTypedArray()
             user[UsersTable.user_behaviour_dates ] = userBehaviourDatesSqlDate?.toTypedArray()

         }
     }

 }
}

  2. 0

    It depends on what you want to achieve. If you want have all data in a single field using array column than you have to ask library author to support it.

    Otherwise I would suggest to create a separate table UsersBehaviorDatesTable and use native Exposed references to manage it. You can check that part of wiki

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search