Pass nothing (not null) to the server if the argument is None

Question

I have a model:

case class MyModel(
  id: Pk[Long] = NotAssigned,
  startsAt: Option[DateTime] = None,
  addedAt: Option[DateTime] = None
) 

object MyModel {

// .....................
SQL("""
    INSERT INTO my_table(starts_at)
    VALUES ({startsAt})
    """
    ).on('startsAt -> newItem.startsAt).executeInsert()

}

Both startst_at and added_at have a default value of now() in Postgresql and don't allow null values in them. It doesn't cause any error for addedAt (because I never pass it to the server from the client) but it does cause the error for startsAt if it's not specified at newItem.startsAt and, thus, is equal to None and, thus, it's being passed as null.

org.postgresql.util.PSQLException: ERROR: null value in column "starts_at" violates not-null constraint

What I want is be able to specify startsAt whenever I want it and pass it to the server, meaning if I specify it then that value should be passed to the server, if not - nothing should be passed and the server should use its default value now(). I don't want to specify the default value at the client because it's already set at the server at the db level.

Answer 1

How about this SQL fix:

insert into my_table(starts_at)
values (COALESCE({startsAt}, now())

Updated: requirement is to use the default value of the column

The only way that I know of to get the server to use the default value of a column in an insert, is not to mention that column in the columns list. For example (not tested):

startsAt.map { date =>
  SQL("""insert into my_table(starts_at) values({startsAt})""")
    .on('startsAt -> date)
    .execute()
}.orElse {
  SQL("""insert into my_table() values()""")
    .execute()
}