Syntax error h2 database in postgresql compatibility

Question

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!

score 2 · Accepted Answer · answered Jul 07 '20 at 16:05

2

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.

answered Jul 07 '20 at 16:05

Evgenij Ryazanov

6,960
2
10
18

Thank you very much for your quick answer! – skywalker Jul 07 '20 at 16:12
Can you tell me where I can find these limitations? – skywalker Jul 08 '20 at 07:02
2

It's not a limitation. H2 is not an emulator of PostgreSQL. Documentation of compatibility modes clearly describes that only some deviations and vendor-specific features are supported. https://h2database.com/html/features.html#compatibility – Evgenij Ryazanov Jul 08 '20 at 10:42

Syntax error h2 database in postgresql compatibility

1 Answers1