PostgreSQL同时向两张表插入数据失败，求解决方法

我看了你的代码，问题主要出在两个地方，咱一步一步来改：

问题1：错误的关联ID获取方式 #

你在user_info的INSERT语句里用了(select id from "user")，这会返回user表里所有的ID，而不是你刚插入的那条记录的ID——如果表里有多个数据，这会直接报错；就算是空表，逻辑上也不够严谨，万一同时有其他插入操作就会出问题。正确的做法是直接引用第一个CTEuser_insert返回的user_login_id。

问题2：CTE缺少触发执行的查询 #

PostgreSQL里，仅包含INSERT/UPDATE/DELETE的WITH语句需要在最后加上一个SELECT语句来触发整个操作的执行，不然整个CTE块不会实际运行。

修正后的代码 #

WITH "user_insert" as (
 INSERT INTO "user" ("username", "password")
 VALUES ('larry', 'root')
 RETURNING "id" as "user_login_id"
)
INSERT INTO "user_info"("first_name", "last_name", "email", "street", "city", "state", "zipcode", "phone", "user_login_id")
SELECT 'larry', 'marry', '222@gmail.com', '222 adam street', 'saint paul', 'MN', '55101', '5551113333', "user_login_id"
FROM "user_insert";

或者如果你喜欢保留第二个CTE的写法，也可以这样：

WITH "user_insert" as (
 INSERT INTO "user" ("username", "password")
 VALUES ('larry', 'root')
 RETURNING "id" as "user_login_id"
), "second_insert" as (
 INSERT INTO "user_info"("first_name", "last_name", "email", "street", "city", "state", "zipcode", "phone", "user_login_id")
 SELECT 'larry', 'marry', '222@gmail.com', '222 adam street', 'saint paul', 'MN', '55101', '5551113333', "user_login_id"
 FROM "user_insert"
)
SELECT * FROM "second_insert"; -- 用SELECT触发整个CTE执行

额外提醒 #

• 注意phone字段你定义的是INT类型，但你插入的5551113333已经超过了PostgreSQL INT类型的最大值（2147483647），执行时会报错。建议把phone字段改成VARCHAR(20)类型，毕竟电话号码通常不需要做数值运算，还能兼容带区号、分隔符的格式。
• 这个CTE写法本身是原子性的，PostgreSQL会把所有操作当成一个事务，不用担心出现一张表插入成功、另一张失败的情况。

内容的提问来源于stack exchange，提问作者miss.traveler