Query with casting in WHERE

Question

I'm learning this wonderful library, however while simple queries work, I'm confused how to write something that not in library FAQ.

For example,

create table if not exists ticks
(id bigserial not null constraint ticks_pkey primary key,
    timestamp timestamp not null
);

It it possible to write something like

select coalesce(max(id), 0) from ticks where timestamp::date = ?

Actually, I have 2 issues here

1. column.max() doesn't have any suitable modifiers, for example, function() accepts no parameters. Probably, I can emulate this in code after I fetch the row.

2. I have no idea how to make casting in where or write arbitrary where condition.

Answer 1

If it's possible to map object to your existing table then you could try something like:

object Ticks : LongIdTable() {
    val timestamp = datetime("timestamp ")
}

fun Expression<DateTime>.pgDate() = object : org.jetbrains.exposed.sql.Function<DateTime>(DateColumnType(false)) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        append(this@pgDate, "::date")
    }
}

val expr =  Coalesce(Ticks.id.max(), longLiteral(0))
Ticks.slice(expr).select {
    Ticks.timestamp.pgDate() eq DateTime.parse("2019-01-01")
}