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
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.