时间戳

首页 > 解决方案 > 使用子查询中的数据时如何更新插入（Postgres）

sql - 使用子查询中的数据时如何更新插入（Postgres）

问题描述

我有两张桌子：

在这种情况下，转让是与航空公司关系的任何容器。

每当有新的分配进入assignments表格时，我想插入（如果是新行则插入或更新，如果它是分配给新航空公司的现有容器）我的rls_permissions表格。

我在使用 upsert 时遇到的问题，特别是 ON CONFLICT ON CONSTRAINT，是我的插入语句包含要插入的数据的子查询，因此我不确定如何编写语句的 DO UPDATE SET 部分

我已经尝试使用“排除”来尝试根据之前的冲突挑选出我想要更新的assignedCarrier，但是我一直收到“错误：列excluded.receptacleId不存在”

我的 pkey 看起来像这样：

CREATE UNIQUE INDEX rls_permissions_pkey ON rls_permissions("receptacleId" text_ops);

虚拟数据可能是：

receptacleID    assignedCarrier
aaaaaaaaaa00    AA

AA 是“美国航空公司”

INSERT INTO rls_permissions ("receptacleId","rlsUserId") 
SELECT DISTINCT assignments."receptacleId", assignments."assignedCarrier" 
FROM assignments
ON CONFLICT ON CONSTRAINT rls_permissions_pkey
DO UPDATE SET "rlsUserId" = (SELECT DISTINCT assignments."assignedCarrier" 
                             FROM assignments 
                             WHERE assignments."receptacleId" = excluded."receptacleId");

例外的结果是，如果没有冲突，则将从子查询返回的数据插入到权限表的新行中。

如果存在冲突，我只想更新新分配的承运人，而不是更新或插入新行，因为该容器已经存在。

标签： sqlpostgresqlsubqueryupsert

解决方案

您不需要 UPDATE 部分中的子查询。excluded您可以通过关键字访问 INSERT 部分的值。

INSERT INTO rls_permissions ("receptacleId","rlsUserId") 
SELECT DISTINCT assignments."receptacleId", assignments."assignedCarrier" 
FROM assignments
ON CONFLICT ON CONSTRAINT rls_permissions_pkey
DO UPDATE SET "rlsUserId" = excluded."rlsUserId";

对的引用是excluded."rlsUserId"指将插入到列 rlsUserId 中的值，因此它是assignments."assignedCarrier"从您的 SELECT 语句中检索到的值。

推荐阅读