1

I have a project with Java 11 + Spring Boot 2 + Liquibase + Postgres 11. There is a task to make SQL dump of table from a legacy Oracle DB and craft a migration from it. Table has a BYTEA columnt with small GIF images (~400 bytes, tiny icons). Migration should be applied successfuly not only to Postgres, but to H2 in test as well. But there is an issue, that H2 and Postgress, seemingly, have a different syntax for HEX literals, when inserting into a BYTEA column. As far as I googled, H2 requires X'0123456789ABCDEF' and Postgres requires '\x0123456789ABCDEF'. When I try to apply the migration to H2 with Postgres HEX syntax, an exception "Hexadecimal string contains non-hexadecimal characters" is thrown. Are there any workarounds or proper way to do such a thing?

Improve this question
5
  • 3
    Does this post helps you? It suggests to use PostgreSQL decode(filedata-in-hex, 'hex') function. Commented Jun 4, 2021 at 6:44
  • 2
    @RakhiAgrawal hey, thanks a lot, it works, but I have chosen to use Liquibase feature to bypass the issue of the question. Liquibase allows to have 2 separate changesets in 1 SQL formatted file, demarcated by --changeset author:id dbms:h2 and --changeset author:id dbms:postgresql. Very convinient attribute. Commented Jun 4, 2021 at 7:28
  • great! Thanks for the another approach. Will prove helpful to others in future. Commented Jun 4, 2021 at 8:46
  • 1
    Does this answer your question? Transition H2 data to PostgreSQL Commented Jun 4, 2021 at 12:48
  • 1
    @AbdelrhmanArnos it has already been suggested in the first comment. It works, thx. Commented Jun 4, 2021 at 17:56
Related questions
2
Hibernate Not Loading Postgresql bytea consistently
14
PostgreSQL: BYTEA vs OID+Large Object?
1
bytea in postgres storing and retrieving bytes
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.