JDBC Insert with Postgres Enum

Question

Given the following Person SQL and model code:

SQL

CREATE TYPE sex AS ENUM ('male', 'female');

CREATE TABLE person (
    id bigserial primary key,
    name varchar(100) NOT NULL,
    age integer NOT NULL,
    gender sex NOT NULL );

Code

object Person {

    sealed trait Gender 
    case object Male extends Gender 
    case object Female extends Gender 

    // credit to Travis Brown: http://stackoverflow.com/a/30946172/409976
    implicit val GenderShows: Show[Gender] = Show.shows {
      case Male   => "male"
      case Female => "female"
    }

}

case class Person private(id: Option[Long], name: String, age: Int, gender: Person.Gender)

I wrote the following object for creating a Person:

object PostgresRepository  {

    val xa = DriverManagerTransactor[IO](
      "org.postgresql.Driver", "jdbc:postgresql:person", "postgres", "postgres"
    )

    def insert1(name: String, age: Int, gender: Gender) =
        sql"insert into person (name, age, gender) values 
            ($name, $age, 
       ${implicitly[Show[Gender]].shows(gender)})".update.run.transact(xa)
}

Then, I ran sbt console, and tried to insert:

scala> PostgresRepository.insert1("foo", 3, net.phone.model.Person.Male)
res0: scalaz.effect.IO[Int] = scalaz.effect.IOFunctions$$anon$6@2697b43b

scala> res0.unsafePerformIO
org.postgresql.util.PSQLException: ERROR: column "gender" is of type sex but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 56

Answer 1

As the exception already suggested, you need to cast the enum value expression.

def insert1(name: String, age: Int, gender: Gender) = {
    val sgender = implicitly[Show[Gender]].shows(gender)
    sql"insert into person (name, age, gender)
          values ($name, $age, CAST($sgender AS sex))
       ".update.run.transact(xa)
}