CODEWITHSUNDEEP
h2ddl
skywalker
skywalker

Reputation: 716

Syntax error h2 database in postgresql compatibility

When the following snippet is executed in h2 databse (compatibility POSTGRESQL) I encounter syntax error which seems to be reported by h2:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "
                                                                      
             
                                                                      

ALTER TABLE ACTIVITIES
    ADD CONSTRAINT FK_ACT_NEXACTID FOREIGN KEY (NEXTACTIVITYID)
    REFERENCES ACTIVITIES (ID)
    DEFERRABLE INITIALLY[*] DEFERRED
 "; SQL statement:

----------------------------------------------------------------------
-- ACTIVITIES
----------------------------------------------------------------------

ALTER TABLE ACTIVITIES
    ADD CONSTRAINT FK_ACT_NEXACTID FOREIGN KEY (NEXTACTIVITYID)
    REFERENCES ACTIVITIES (ID)
    DEFERRABLE INITIALLY DEFERRED
 [42000-198]

SQL State is 42000 which is the code for syntax error. When I execute it on native postgre server it runs without errors. Any help is appreciated. I read here H2 - (Quite) long INSERT failing with error 42000 that some extra character can be the reason for this but I do not see any. Thank you!

Upvotes: 0

Views: 3304

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

You're using a vendor-specific syntax supported by PostgreSQL and some other DMBS. Compatibility modes of H2 provide only very limited compatibility with others. You should test your application with the same DBMS as you use in production, especially when you want to use some vendor-specific features.

If you really need to use H2 here for a some reason, you need to remove DEFERRABLE INITIALLY DEFERRED from your command when you work with H2 and your application obliviously must be able to work with regular constraints, because H2 doesn't support deferred ones. Also 1.4.198 is a beta-quality version of H2, you shouldn't use it without a really good reason, but never versions for now don't support deferred constraints too.

Upvotes: 2

Related Questions