Unable to create table with postgres-hstore column type in H2 Database

Question

I'm working on requirement to create a new table which has a column of type hstore. This is a Spring Boot app. I have created the JPA entity and run some tests connecting with the live posgres DB and I'm able to write the JSON data (through the form of Hashmap) into the hstore column.

Now i want to read/write data from this table and automate the test cases by connecting through H2 database. I have created the .sql file for the table. while starting the application I'm getting the below error,

    Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script 
    statement #1 of class path resource [test/db/my_table.sql]: 
    CREATE EXTENSION hstore; nested exception is java.sql.SQLSyntaxErrorException:
    unexpected token: EXTENSION

I'm aware that in order to create a table with hstore column type , first I need to create the Hstore extension. this is the way I did it in postgres pg admin while creating the actual table

This is my .sql file

    CREATE EXTENSION hstore;

    CREATE TABLE my_table
    (
    id numeric (10,0) NOT NULL,
    sample_id numeric (10,0),
    attr hstore,
    date_inserted timestamp with time zone DEFAULT now() NOT NULL,
    date_updated timestamp with time zone DEFAULT now() NOT NULL,
    inserted_by character varying(30) DEFAULT 'MY_SERVICE' NOT NULL,
    updated_by character varying(30) DEFAULT 'MY_SERVICE' NOT NULL
    );

    INSERT INTO my_table (id, sample_id, attr) VALUES
    (101, 43534, '"isValidate"=>"true", "categoryType"=>"3"');

Can someone guide me whether H2 database supports postgres hstore column type ? or is there any way to achieve this ?

Answer 1

I was facing the same issue as you, and the following helped me in a Spring Context:

1. Create a file init.sql in the resources with the following SQL code:

CREATE DOMAIN IF NOT EXISTS HSTORE AS other;

2. Insert ou update the attribute spring.datasource.url in your properties file as follows:

spring.datasource.url=jdbc:h2:mem:testdb;MODE=PostgreSQL;INIT=RUNSCRIPT
FROM 'classpath:init.sql'

In your case, I guess would be only needed to add the command CREATE DOMAIN IF NOT EXISTS HSTORE AS other; just above the command CREATE EXTENSION hstore;