插入语句，从源表返回列

Question

对于批量插入，我有一个外部参考值。作为插入的一部分，我想返回新创建记录的参考和相应id，以便在另一个系统中创建映射记录。使用"RETURNING"可以在目标表上很好地工作。除了在目标表中创建虚拟列，还有其他方式可以实现我所要做的吗？绝对不想逐行处理。注意:当前正在使用10.7版本。在我的示例代码中，我尝试了"returning id，source.ref"，但显然这不受支持。

create table test( id serial primary key, name varchar);

insert into test( name)
select source.name
from ( values('refa', 'name a'), ('refb', 'name b'), ('refc', 'name c') ) source(ref, name)
returning id --, source.ref

- Con

Answer 1

如果源代码是唯一的，请参考它。可以尝试以下方式：

WITH q AS ( INSERT  INTO test (name)
              SELECT source.name
              FROM ( VALUES ('refa', 'name a'), ('refb', 'name b'), ('refc', 'name c')
                   ) AS source (ref, name)
            RETURNING * )
SELECT q.id, source.ref
FROM q
  JOIN ( VALUES ('refa', 'name a'), ('refb', 'name b'), ('refc', 'name c')
       ) AS source (ref, name) ON q.name = source.name

---

但是如果您想将此映射添加到另一个表中，您可能需要重新构建查询，例如：

INSERT INTO mapping ( ref, id )
  SELECT source.ref, ( INSERT INTO test (name) VALUES ( source.name ) RETURNING id )
  FROM ( VALUES ('refa', 'name a'), ('refb', 'name b'), ('refc', 'name c')
                 ) AS source (ref, name) )

- Usagi Miyamoto

---

这让我接近答案，但由于需要唯一的名称，它并不理想。 - Con

Answer 2

使用CTE：

WITH a AS (
   INSERT ...
   RETURNING ...
), b AS (
   INSERT ...
   RETURNING ...
)
SELECT ...
FROM a JOIN b ON ...

- Laurenz Albe

---

请问您能否澄清如何使用CTE编写问题中的原始查询？虽然我还没有喝上早晨的咖啡，但我还是看不到它... :) - Joachim Isaksson

好的，这里涉及到猜测。OP说他有一个外键引用，所以我猜他想在两个表中插入一行并返回生成的主键。我承认这是一个冒险的猜测。 - Laurenz Albe

嗨Laurenz，数据来自单独的系统，并且只有1个目标表。源表具有我的外键，并希望作为插入的一部分获取一些映射信息。我是Postgres的新手，通常使用SQL Server工作，并且通常可以通过简单的合并语句实现此目的。 - Con

你可以用一个SELECT或其他返回外键的语句替换第一个INSERT。 - Laurenz Albe