search

zio / zio-quill

Compile-time Language Integrated Queries for Scala
https://zio.dev/zio-quill
Apache License 2.0
2.14k stars 347 forks source link

Exception in Postgres inserting None value for Option[JsonValue[...]] or Option[JsonbValue[...]] fields #3022

Closed vladimirkl closed 2 months ago

vladimirkl commented 2 months ago

Version: v4.8.3 Module: quill-jdbc-zio Database: Postgres

Expected behavior

It should be possible to define case class with Option[JsonValue[...]] or Option[JsonbValue[...]] field, set value to None, and save to Postgres table with nullable json or json column using quill query with insertValue(...)

Actual behavior

Postgres throws exception when saving None values for Option[JsonValue[...]] or Option[JsonbValue[...]] fields: ERROR: column "..." is of type json but expression is of type character varying

Steps to reproduce the behavior

 case class PersonJson(name: String, age: Int)
 case class JsonOptEntity(name: String, value: Option[JsonValue[PersonJson]])

 val jsonOptQuery  = quote(querySchema[JsonOptEntity]("JsonEntity"))
 val value = JsonOptEntity("JoeEntity", None)
 testContext.run(jsonOptQuery.insertValue(lift(value)))  // throws exception

Similar reproduction is for Option[JsonbValue[...]]

Workaround

override entityEncoder and astEncoder methods in custom Postgres context and set sqlType to Types.OTHER.

@getquill/maintainers