CODEWITHSUNDEEP
postgresqljdbch2embedded-database
shaiksphere
shaiksphere

Reputation: 325

h2 database in PostgreSQL mode does not accept PostgreSQL SQL syntax

Here is my h2 database url (I am using h2 database in in-memory-embedded mode. Also I mentioned that mode is PostgreSQL):

database.url=jdbc\:h2\:mem\:test;MODE\=PostgreSQL;DB_CLOSE_DELAY\=-1

So, I expect H2 database to execute SQL statements that confirm to PostgreSQL database. Here is an example:

CREATE TABLE audit_entries (
    id bigint NOT NULL,
    "time" timestamp without time zone NOT NULL,
    message_id character varying(255) NOT NULL,
    user_id character varying(255)
);

I am getting following error when I run my integration tests that use h2db:

> SQL State  : 42001
Error Code : 42001
Message    : Syntax error in SQL statement "CREATE TABLE AUDIT_ARGUMENTS (
    ENTRY_ID BIGINT NOT NULL,
    STRING_VALUE CHARACTER VARYING(255),
    NUMBER_VALUE DOUBLE PRECISION,
    TIMESTAMP_VALUE TIMESTAMP WITHOUT[*] TIME ZONE,
    ARG_INDEX INTEGER NOT NULL
) "; expected "WITH, (, FOR, UNSIGNED, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
CREATE TABLE audit_arguments (
    entry_id bigint NOT NULL,
    string_value character varying(255),
    number_value double precision,
    timestamp_value timestamp without time zone,
    arg_index integer NOT NULL
) [42001-195]
Location   : upgrade/postgresql/V1_0_0__initial.sql (/Users/<user>/Documents/<productname>/target/resources/main/upgrade/postgresql/V1_0_0__initial.sql)
Line       : 70
Statement  : CREATE TABLE audit_arguments (
    entry_id bigint NOT NULL,
    string_value character varying(255),
    number_value double precision,
    timestamp_value timestamp without time zone,
    arg_index integer NOT NULL
)

    at com.<company-name>.database.Upgrades.upgrade(Upgrades.java:161) ~[main/:na]
    at com.<company-name>.t1.WebAppMain.upgrade(WebAppMain.java:336) [main/:na]
    at com.<company-name>.t1.WebAppMain.start(WebAppMain.java:169) [main/:na]
    at com.<company-name>.t1.WebAppMain.contextInitialized(WebAppMain.java:151) [main/:na]
    at org.eclipse.jetty.server.handler.ContextHandler.callContextInitialized(ContextHandler.java:800) [jetty-server-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.servlet.ServletContextHandler.callContextInitialized(ServletContextHandler.java:444) [jetty-servlet-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.server.handler.ContextHandler.startContext(ContextHandler.java:791) [jetty-server-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.servlet.ServletContextHandler.startContext(ServletContextHandler.java:294) [jetty-servlet-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.webapp.WebAppContext.startWebapp(WebAppContext.java:1349) [jetty-webapp-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.webapp.WebAppContext.startContext(WebAppContext.java:1342) [jetty-webapp-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.server.handler.ContextHandler.doStart(ContextHandler.java:741) [jetty-server-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.webapp.WebAppContext.doStart(WebAppContext.java:505) [jetty-webapp-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.akhikhl.gretty.JettyWebAppContext.super$10$doStart(JettyWebAppContext.groovy) [gretty-runner-jetty9-1.4.0.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_111]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_111]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_111]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_111]
    at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:93) [groovy-2.4.5.jar:2.4.5]
    at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:325) [groovy-2.4.5.jar:2.4.5]
    at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1210) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodOnSuperN(ScriptBytecodeAdapter.java:132) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodOnSuper0(ScriptBytecodeAdapter.java:152) [groovy-2.4.5.jar:2.4.5]
    at org.akhikhl.gretty.JettyWebAppContext.doStart(JettyWebAppContext.groovy:44) [gretty-runner-jetty9-1.4.0.jar:na]
    at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:68) [jetty-util-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.util.component.ContainerLifeCycle.start(ContainerLifeCycle.java:132) [jetty-util-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.util.component.ContainerLifeCycle.doStart(ContainerLifeCycle.java:114) [jetty-util-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.server.handler.AbstractHandler.doStart(AbstractHandler.java:61) [jetty-server-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.server.handler.ContextHandlerCollection.doStart(ContextHandlerCollection.java:163) [jetty-server-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:68) [jetty-util-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.util.component.ContainerLifeCycle.start(ContainerLifeCycle.java:132) [jetty-util-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.server.Server.start(Server.java:387) [jetty-server-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.util.component.ContainerLifeCycle.doStart(ContainerLifeCycle.java:114) [jetty-util-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.server.handler.AbstractHandler.doStart(AbstractHandler.java:61) [jetty-server-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.server.Server.doStart(Server.java:354) [jetty-server-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:68) [jetty-util-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.eclipse.jetty.util.component.LifeCycle$start$0.call(Unknown Source) [jetty-util-9.2.15.v20160210.jar:9.2.15.v20160210]
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117) [groovy-2.4.5.jar:2.4.5]
    at org.akhikhl.gretty.JettyServerManager.startServer(JettyServerManager.groovy:45) [gretty-runner-jetty-1.4.0.jar:na]
    at org.akhikhl.gretty.ServerManager$startServer$0.call(Unknown Source) [gretty-runner-1.4.0.jar:na]
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125) [groovy-2.4.5.jar:2.4.5]
    at org.akhikhl.gretty.Runner.run(Runner.groovy:117) [gretty-runner-1.4.0.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_111]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_111]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_111]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_111]
    at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite$PogoCachedMethodSiteNoUnwrapNoCoerce.invoke(PogoMetaMethodSite.java:210) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite.call(PogoMetaMethodSite.java:71) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113) [groovy-2.4.5.jar:2.4.5]
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117) [groovy-2.4.5.jar:2.4.5]
    at org.akhikhl.gretty.Runner.main(Runner.groovy:44) [gretty-runner-1.4.0.jar:na]

Upvotes: 3

Views: 6900

Answers (1)

BioQwer
BioQwer

Reputation: 71

As @craig-ringer said in comments

H2 tries to emulate PostgreSQL, but it is not PostgreSQL. Some details of syntax may vary. The error message is spectacularly useless so it's hard to say exactly what it's complaining about, maybe the "timestamp without time zone"? Try just "timestamp"

"timestamp without time zone"-> "timestamp"

This is work for me

Upvotes: 1

Related Questions