主页 / dba / 问题 / 246447
Accepted
Zach Smith
Asked: 2019-08-29 05:49:20 +0800 CST

是否可以在 PostgreSQL 中使用单个语句将其插入到自引用表中？

  • 772

如果我有一张桌子A，像这样：

A {
 id SERIAL
 title TEXT
 ...
 parentId INT references A.id via a foreign key constraint
}

我正在从A_SOURCE没有parentId列的源表中提取数据。取而代之的是一parentTitle列。所以源表看起来像这样：

A_SOURCE {
  title TEXT
  parentTitle TEXT
}

在我意识到我无法轻松地将源中的列解析为目标中的列之前，我开始编写一个 upsert 语句以A通过从表中选择插入到表中。A_SOURCEparentTitleparentId

由于我无法确定在处理子项时是否已插入父项，因此连接或子查询可能不会返回任何结果。

我的 upsert 声明看起来像这样：

with source as (
  select
   title
   parentTitle
  from A_SOURCE 
)

insert into A
select
title
... I don't think I can resolve to parentId here?
from source
on concflict ...;

我知道我可以运行两个单独的语句：

  1. 插入null作为 parentId
  2. 然后更新第二条语句中的 parentId 字段

但是可以在单个查询中执行此操作吗？

postgresql upsert

1 个回答

  1. Best Answer

    使用递归 CTE 准备要插入的值，该 CTE 预先计算ids 并定义顺序。然后按顺序插入：

    WITH RECURSIVE a_sort AS (
      /* get all entries without a parent (rank 1) */
      SELECT nextval('a_id_seq')::integer AS id,
             title,
             NULL::integer AS parentid,
             parenttitle,
             1 AS rank
      FROM a_source
      WHERE parenttitle IS NULL
   UNION ALL
      /* recursively get all the immediate children and increase rank */
      SELECT nextval('a_id_seq')::integer,
             src.title,
             a_sort.id,
             a_sort.title,
             a_sort.rank + 1
      FROM a_source AS src
         JOIN a_sort ON a_sort.title = src.parenttitle
)
INSERT INTO a
SELECT id,
       title,
       parentid
FROM a_sort
ORDER BY rank;

    该解决方案的美妙之处在于它使用了后面的序列a.id（a_id_seq在示例中），因此在我们完成后序列自动具有正确的值。

    该解决方案假定数据a_source正确，即不包含循环。

    • 3

相关问题