Stub Postgresql JSON type with HSQLDB
<p>I am looking for a way to stub Postgresql JSON type in HSQLDB. I need it for unit testing.</p>

<p>Specifying a JDBC URL parameter <code>sql.syntax_pgs=true</code> does not work so I created a <code>schema-hsqdbl.sql</code> script which Spring Boot picks up automatically upon start.</p>

<p>I tried to stub this type in following ways.</p>

<pre><code>DROP TYPE json IF EXISTS;
CREATE TYPE json AS varbinary(1000000);

DROP TYPE json IF EXISTS;
CREATE TYPE json AS binary;

DROP TYPE json IF EXISTS;
CREATE TYPE json AS text;

DROP TYPE json IF EXISTS;
CREATE TYPE json AS other;
</code></pre>

<p>Unfortunately all of that fails upon persisting an instance of my entity with</p>

<pre class="lang-none prettyprint-override"><code>java.sql.SQLSyntaxErrorException: incompatible data type in operation
</code></pre>

<p>For a real mapping into Postgresql JSON I use the Vlad Mihalcea's <a href="https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/" rel="nofollow noreferrer">solution</a></p>