“WITH AS” Working in Postgres but not in H2 dabatabse

I am writing a single query to insert data into 2 tables using “WITH AS”. The query works fine on Postgres but on H2 database it is throwing syntax error.

I have 2 tables. Table 1 has 2 columns — a Primary Key table1_ID and a table1_value column. Table 2 has 3 columns — a PK table2_Id and table2_value and table1_id as Foreign key.

The query is like this:

WITH ins as (
    INSERT INTO table_1 (table1_value) VALUES ("table1_value")
    RETURNING table1_ID as t1_id
   )
   INSERT INTO table_2 (table2_value, tab1_id) VALUES ("table2_value", (SELECT t1_id FROM ins));

This query works fine on Postgres but on H2 DB it throws syntax error with a message

“; expected “(, WITH, SELECT, FROM”; SQL statement

Answer

hadatabase reference link:
http://www.h2database.com/html/advanced.html#recursive_queries
http://www.h2database.com/html/commands.html?highlight=insert&search=insert#firstFound

---

see Compatibility section: https://www.postgresql.org/docs/current/sql-insert.html

INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension, as is the ability to use WITH with INSERT, and the ability to specify an alternative action with ON CONFLICT. Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, is disallowed by the standard.