Problems mapping Option[Blob] to Postgres type

[nathanie...@gmail.com]

I am trying to adapt an existing Slick-based project (not mine) to use Postgres instead of MySQL.  The column is defined in Scala as an Option[Blob], which maps directly to the blob type in MySQL.  Everything I found online indicated that in Postgres the appropriate equivalent is "bytea", which is easy enough to get in the database migrations, but when the program tries to do an insert, it fails with this error:

org.postgresql.util.PSQLException: ERROR: column "IMPORTS_ZIP" is of type bytea but expression is of type bigint

  Hint: You will need to rewrite or cast the expression.

I found many references to this problem online, but they all appear to be related to a package called Hibernate, so it's totally unclear how to manage this in Slick.  Is this technically possible, and if so what is the appropriate solution?

PS. Postgres 9.6.1, JDBC driver 42.2.5, Slick 3.2.3

thanks,

Nat

[nathanie...@gmail.com]

Update: if I try mapping to "oid" type instead, the insert works - but I get this error trying to retrieve the row later:

org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit

mode.

        at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager

.java:254)

        at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager

.java:240)

        at org.postgresql.jdbc.AbstractBlobClob.getLo(AbstractBlobClob.java:272)

        at org.postgresql.jdbc.AbstractBlobClob.length(AbstractBlobClob.java:101

)

...

The upstream code is trying to convert the Option[Blob] to a raw string.  Unfortunately the SELECT query is already being run transactionally, so it's not really clear to me what's going on under the hood that causes it to choke on the conversion.  I suspect it might be something like this: https://stackoverflow.com/a/41219465

Not sure if this helps at all, I'm just trying to go through all of the possible approaches to see if anything magically works.

-Nat