主页 / dba / 问题 / 315774
Accepted
Biller Builder
Asked: 2022-08-19 04:35:45 +0800 CST

您如何使用 CTE 编写有序的多插入？

  • 772

像这样的表：

CREATE TABLE test_1 (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE test_2 (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE test_refs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  id_1 bigint NOT NULL REFERENCES test_1,
  id_2 bigint NOT NULL REFERENCES test_2
);

和这样的插入查询：

WITH new_test_1_rows AS (
  INSERT INTO test_1
  DEFAULT VALUES
  RETURNING *
), new_test_2_rows AS (
  INSERT INTO test_2
  DEFAULT VALUES
  RETURNING *
), test_row_pairs AS (
  INSERT INTO test_refs
   ( id_1, id_2 )
  VALUES
    (
      (SELECT id FROM new_test_1_rows),
      (SELECT id FROM new_test_1_rows)
    )
  RETURNING *
)
SELECT *
FROM test_row_pairs

基本上它的作用：

  • 插入一行test_1
  • 插入一行test_2
  • 将他们的 ID 对插入test_refs

问题是我想将查询重写为多插入查询，即插入的行将行插入n，然后为插入的值创建行。为此，我需要知道 CTE 中的行索引，因此它可以用作连接键。你可以在条款中做些什么吗？test_1ntest_2ntest_refsRETURNING

postgresql insert

1 个回答

  1. Best Answer

    ...对于n插入的行，test_1将 n 行插入test_2 ，然后为插入的值创建n行。test_refs为此，我需要知道 CTE 中的行索引，因此它可以用作连接键。RETURNING 你可以在条款中做些什么吗？

    没有在子句中添加行号的规定RETURNING- 没有等效WITH ORDINALITY项可以与 set-returning 函数一起使用。看：

    如果你添加一个窗口函数（就像你可能已经尝试过的那样），你会得到：

    ERROR:  window functions are not allowed in RETURNING

    Postgres 特定的ROWS FROM语法可以在锁步中返回行，但这也仅适用于返回集合的函数。看：

    您必须走很长一段路，并在另一个 CTE 或子查询中生成行号。两行示例：

    WITH new_test_1_rows AS (
   INSERT INTO test_1 VALUES (DEFAULT), (DEFAULT)
   RETURNING *
   )
, new_test_2_rows AS (
   INSERT INTO test_2 VALUES (DEFAULT), (DEFAULT)
   RETURNING *
   )
INSERT INTO test_refs (id_1, id_2)
SELECT t1.id, t2.id
FROM  (
   SELECT row_number() OVER (ORDER BY id) AS rn, id  -- !
   FROM new_test_1_rows
   ) t1
JOIN  (
   SELECT row_number() OVER (ORDER BY id) AS rn, id  -- !
   FROM new_test_2_rows
   ) t2 USING (rn)  -- !
RETURNING *

    db<>在这里摆弄

    • 0

相关问题