Using Scala Slick with database enums

Question

Using Slick 3 and PostgreSQL, I need to query and update a table that has an column of type enum:

create type WeekDay as ENUM('sun','mon','tue','wed','thu','fri','sat');

create table shifts(
    id serial PRIMARY KEY,
    user_id INTEGER,
    branch_id INTEGER,
    start INTEGER,
    duration INTEGER,
    day WeekDay
    -- foreign keys etc.
);

I tried using Slick's MappedColumnType[java.time.DayOfWeek,String], but this does not satisfy Postgres' type checker (TBH, rightfully):

org.postgresql.util.PSQLException: ERROR: column "day" is of type weekday but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 92
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2412)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2125)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction.$anonfun$run$15(JdbcActionComponent.scala:520)

Table class:

class ShiftTable(tag:Tag) extends Table[Shift](tag, "shifts") {
  import Mappers.dayOfWeekMapper
  def id = column[Long]("id", O.AutoInc, O.PrimaryKey)
  def userId = column[Long]("user_id")
  def branchId = column[Long]("branch_id")
  def start = column[Int]("start")
  def duration = column[Int]("duration")
  def day = column[DayOfWeek]("day")    // <- problematic column

  // keys etc/
  def * = (id, userId, branchId, start, duration, day) <> (Shift.tupled, Shift.unapply)
}

How do I map a Scala value to a custom PostgreSQL type?

Looks like someone already bumped into the same problem: https://stackoverflow.com/questions/22945485/how-to-map-postgresql-custom-enum-column-with-slick2-0-1 — amorfis, Feb 24 '20 at 23:27
Thanks! I'm working with slick-pg in other projects, but I was hoping for a solution that would not require additional libraries, as I thought that problem is small enough to be solved locally :-( — Michael Bar-Sinai, Feb 27 '20 at 17:10

score 4 · Accepted Answer · answered Mar 04 '20 at 10:33

It looks like you can map to an enum using a String without pg-slick.

The trick is to add a cast to the database, as noted in PG JDBC Issue 1420:

CREATE CAST (character varying AS WeekDay) WITH INOUT AS ASSIGNMENT

The documentation for CAST describes WITH INOUT (avoids writing a function for casting) and AS ASSIGNMENT (to implicitly apply the cast when assigning a value).

You can then use a MappedColumnType.base[java.time.DayOfWeek, String] to defining the mapping from the column to DayOfWeek.

I've placed a runnable example here: https://github.com/d6y/pg-enum/blob/master/src/main/scala/main.scala

Using Scala Slick with database enums

1 Answers1