HSQL postgres dialog not recognized

Question

I want to use HSQL for integration tests. Therefore I want to setup the test schema with exact the same script I use for production. This is in postgresql dialect. In the test script I tried to set the dialect but it doesn't seem to work.

At least for uuid datatype and constraints I get syntax error exceptions. E.g. I get a:

CREATE TABLE testtable ( id bigint NOT NULL, some_uuid uuid NOT NULL, name character varying(32) NOT NULL, CONSTRAINT testtable PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); nested exception is java.sql.SQLSyntaxErrorException: type not found or user lacks privilege: UUID

for the following script:

SET DATABASE SQL SYNTAX PGS TRUE;

CREATE TABLE testtable
(
  id bigint NOT NULL,
  some_uuid uuid NOT NULL,
  name character varying(32) NOT NULL,
  CONSTRAINT testtable PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

And I get:

Failed to execute SQL script statement #2 of class path resource [setupTestData.sql]: CREATE TABLE testtable ( id bigint NOT NULL, name character varying(32) NOT NULL, CONSTRAINT testtable PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); nested exception is java.sql.SQLSyntaxErrorException: unexpected token: (

for this script:

SET DATABASE SQL SYNTAX PGS TRUE;

CREATE TABLE testtable
(
  id bigint NOT NULL,
  --some_uuid uuid NOT NULL,
  name character varying(32) NOT NULL,
  CONSTRAINT testtable PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

Answer 1

HSQLDB 2.3.4 and later supports UUID.

HSQLDB does not currently support the PostgreSQL extension WITH (ODS= FALSE)