＆quot＆quot＆quot在Postgres工作，但在H2 Dabatabse中工作

发布于 2025-01-23 08:11:52 字数 628 浏览 3 评论 0 原文

我正在编写一个查询，以使用“ with as”将数据插入2个表中。该查询在Postgres上工作正常，但是在H2数据库上，它正在抛出语法错误。

我有2张桌子。表1具有2列 - 主键 table1_id 和 table1_value 列。表2有3列 - pk table2_id 和 table2_value 和 table1_id 作为外键。

查询是这样的：

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));

此查询在Postgres上正常工作，但是在H2 dB上它会引发语法错误，并带有消息

“;预期”（，与“从”; SQL语句

原文

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