主页 / dba / 问题 / 61365
Accepted
Joishi Bodio
Asked: 2014-03-21 07:22:56 +0800 CST

INSERT 使用 CTE INSERT 的结果来提供唯一的 id 值

  • 772

我正在写一份将数据从旧设计转换为新设计的工作。在这个过程中，我需要将插入中的 id 放入单独的表中，并将其用于插入到目标表中，如下所示：

CREATE TABLE t1 {
  t1_id BIGSERIAL,
  col1 VARCHAR
};
CREATE TABLE t2 {
  t2_id BIGSERIAL,
  col2 VARCHAR, -- renamed from col1 to avoid confusion
  t1_id BIGINT REFERENCES t1.t1_id
};

我定义了与以下形式匹配的 SQL：

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, (SELECT * FROM ins)
FROM t3 a;

我希望它为..SELECT * FROM ins的每一行运行SELECT.. 但它只运行一次并将该值用于 . 中的所有行SELECT。如何重组我的 SQL 以获得所需的行为？

编辑4

t1 最终看起来像：

1,<NULL>
(1 row)

t2 最终看起来像：

10,'a',1
11,'b',1 -- problem with id from t1 being 1
12,'c',1 -- problem with id from t1 being 1
.
.

我希望 t1 看起来像什么：

1,<NULL>
2,<NULL>
3,<NULL>
.
.

我希望 t2 看起来像什么：

10,'a',1
11,'b',2 -- id from t1 of 2
12,'c',3 -- id from t1 of 3
.
.

编辑 为了解决 a_horse_with_no_name 所说的问题，我也尝试了这个（结果相同）：

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, b.t1_id
FROM t3 a
JOIN ins b ON TRUE;

编辑2 我只是尝试SEQUENCE在我的查询中直接引用适当的，并且确实有效 - 但我根本不喜欢那个解决方案（主要是因为我不喜欢硬编码对象名称。）如果有任何其他解决方案比直接引用名称SEQUENCE我会很感激。:)

编辑3 我想另一种解决方案是使用 a代替 CTE .. 但我仍然会欣赏选项/建议PROCEDURE。INSERT

postgresql postgresql-9.2

1 个回答

  1. Best Answer

    如果它们只有 1-1 关系，我不明白为什么需要 2 个表。但这里是（pk是 的主键t3）：

    WITH ins AS (
  INSERT INTO t1 (col1) 
    SELECT NULL FROM t3 
  RETURNING t1_id
) 
, r AS
( SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
) 
, t AS
( SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
) 
INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t 
  JOIN r USING (rn) ;

    如果您的 t3 是 SELECT 而不是预先存在的表的结果，您可以这样实现它，这样您就不必重复 t3 查询两次：

    WITH t3 AS (
  SELECT ...
), ins AS (
  INSERT INTO t1 (col1)
    SELECT NULL FROM t3
  RETURNING t1_id
), r AS (
  SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
), t AS (
  SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
) INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t 
  JOIN r USING (rn);
    • 13

相关问题