Use UUID in Doobie SQL update

Question

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.

have you tried using java UUID instead of String inside your code? java.util.UUID.fromString(YOUR_UUID) — AminMal, Apr 02 '22 at 17:16
[this](https://stackoverflow.com/questions/64338748/how-do-i-convert-a-java-util-uuid-to-doobie-syntax-sqlinterpolator-singlefragmen) also might help — AminMal, Apr 02 '22 at 17:18
Many thanks. `cast` suggested by @a_horse_with_no_name works nicely. I tried `UUID.fromString` but couldn't get this to work, though I shall try again. But @AminMal also kindly pointed out the following link which does look very nice (to keep code succinct) - I'll give it a go. — D Ainslie, Apr 02 '22 at 18:03
Ah yes. Many thanks @AminMal - Using UUID.fromString in conjunction with the necessary Doobie implicits the following works nicely: ``` 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 ``` Be careful with IntelliJ - It thinks that the import is not necessary. — D Ainslie, Apr 02 '22 at 18:13

score 3 · Accepted Answer · edited Apr 06 '22 at 05:33

3

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

edited Apr 06 '22 at 05:33

zwxi

782
7
9

answered Apr 02 '22 at 18:25

D Ainslie

33
1
6

How do I "mark best answer" from @AminMal? – D Ainslie Apr 02 '22 at 18:27
1

They need to post it as an answer for you to be able to do that. If they do, you can mark it as the answer – zwxi Apr 02 '22 at 18:42

Use UUID in Doobie SQL update

1 Answers1