[SOLVED] Use UUID in Doobie SQL update

Use UUID in Doobie SQL update

I have the following simple (cut down for brevity) Postgres table:

create table users(
  id            uuid NOT NULL,
  year_of_birth smallint NOT NULL
);

Within a test I have seeded data. When I run the following SQL update to correct a year_of_birth the error implies that I'm not providing the necessary UUID correctly.

The Doobie SQL I run is:

val id: String = "6ee7a37c-6f58-4c14-a66c-c17083adff81"
val correctYear: Int = 1980

sql"update users set year_of_birth = $correctYear where id = $id".update.run

I have tried both with and without quotes around the given $id e.g. the other version is:

sql"update users set year_of_birth = $correctYear where id = '$id'".update.run

The error upon running the above is:

org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Solution

Both comments provided viable solutions.

a_horse_with_no_name suggested the use of cast which works though the SQL becomes no so nice when compared to the other solution.

AminMal suggested the use of available Doobie implicits which can handle a UUID within SQL and thus avoid a cast.

So I changed my code to the following:

import doobie.postgres.implicits._

val id: UUID = UUID.fromString("6ee7a37c-6f58-4c14-a66c-c17083adff81") 

sql"update users set year_of_birth = $correctYear where id = $id".update.run

So I'd like to mark this question as resolved because of the comment provided by AminMal