PostgreSQL自引用表 - 如何在脚本中存储父ID？

Question

I've the following table:

DROP SEQUENCE IF EXISTS CATEGORY_SEQ CASCADE;
CREATE SEQUENCE CATEGORY_SEQ START 1;

DROP TABLE IF EXISTS CATEGORY CASCADE;

CREATE TABLE CATEGORY (
  ID        BIGINT                 NOT NULL DEFAULT nextval('CATEGORY_SEQ'),
  NAME      CHARACTER VARYING(255) NOT NULL,
  PARENT_ID BIGINT
);

ALTER TABLE CATEGORY
  ADD CONSTRAINT CATEGORY_PK PRIMARY KEY (ID);
ALTER TABLE CATEGORY
  ADD CONSTRAINT CATEGORY_SELF_FK FOREIGN KEY (PARENT_ID) REFERENCES CATEGORY (ID);

现在我需要插入数据。所以我从父节点开始：

INSERT INTO CATEGORY (NAME) VALUES ('PARENT_1');

现在，我需要刚刚插入的父级的ID，以便向其添加子级：

INSERT INTO CATEGORY (NAME, PARENT_ID) VALUES ('CHILDREN_1_1', <what_goes_here>);
INSERT INTO CATEGORY (NAME, PARENT_ID) VALUES ('CHILDREN_1_2', <what_goes_here>);

如何获取并存储父级 ID，以便稍后在后续插入操作中使用？

- Opal

---

@a_horse_with_no_name，这样行不通。对于两个子元素，我需要相同的PARENT_ID值，而这两个函数都会给我最新的值。 - Opal

Answer 1

您可以使用带有 returning 子句的数据修改 CTE：

with parent_cat (parent_id) as (
   INSERT INTO CATEGORY (NAME) VALUES ('PARENT_1')
   returning id
)
INSERT INTO CATEGORY (NAME, PARENT_ID) 
VALUES 
  ('CHILDREN_1_1', (select parent_id from parent_cat) ), 
  ('CHILDREN_1_2', (select parent_id from parent_cat) );

- user330315

Answer 2

答案是使用RETURNING和WITH一起使用。

WITH inserted AS (
  INSERT INTO CATEGORY (NAME) VALUES ('PARENT_1')
  RETURNING id
) INSERT INTO CATEGORY (NAME, PARENT_ID) VALUES
  ('CHILD_1_1', (SELECT inserted.id FROM inserted)),
  ('CHILD_2_1', (SELECT inserted.id FROM inserted));

- Opal

---

不需要复制@a_horse_with_no_name的答案。请删除... - joop

@joop，这是我的答案，我同时在添加并自己解决了它。 - Opal

Answer 3

(简而言之：选择选项3：带有RETURNING的INSERT)

回想一下，在postgresql中，表没有“id”概念，只有序列（通常但不一定用作代理主键的默认值，使用SERIAL伪类型）。

如果您想获取新插入行的ID，有几种方法：

---

选项1: CURRVAL(<序列名称>);。

例如：

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval('persons_id_seq');

序列的名称必须是已知的，它真的是任意的；在这个例子中，我们假设表 persons 有一个使用 SERIAL 伪类型创建的 id 列。为了避免依赖于此并感觉更加清洁，您可以使用 pg_get_serial_sequence 替代：

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval(pg_get_serial_sequence('persons','id'));

注意事项：currval() 只在 INSERT（执行了 nextval()）之后，在同一会话中才能使用。

---

选项2: LASTVAL();

这与先前的相似，只是您无需指定序列号：它会查找最近修改的序列（始终在您的会话中，同上述警告）。

---

CURRVAL和LASTVAL都是完全并发安全的。PG中序列的行为被设计成不会干扰不同的会话，因此不存在竞争条件的风险（如果另一个会话在我的INSERT和SELECT之间插入了另一行，则我仍然可以获得正确的值）。

但是它们确实存在微妙的潜在问题。如果数据库有一些TRIGGER（或RULE），在向persons表中插入时，在其他表中进行一些额外的插入...那么LASTVAL可能会给我们错误的值。如果额外的插入是在同一个persons表中完成的，甚至CURRVAL也可能出现这个问题（这种情况很少见，但风险仍然存在）。

---

选项3：使用带有RETURNING的{{link1：INSERT}}

INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id;

这是获取ID最干净、高效和安全的方式，它没有任何之前方法的风险。

缺点？几乎没有：你可能需要修改调用INSERT语句的方式（在最坏的情况下，也许你的API或DB层不希望INSERT返回一个值）；它不是标准SQL（谁在乎呢）；自Postgresql 8.2（2006年12月）以来就可用了。

---

结论：如果可以的话，请选择选项3。否则，优先选择1。

注意：如果您打算获取全局插入的最后一个id（不一定在您的会话中），那么所有这些方法都是无用的。对于这种情况，您必须使用select max(id) from table（当然，这不会读取其他事务的未提交插入）。

- Ahmed Jehanzaib

---

选项3不能在普通SQL脚本中使用。 - user330315

我该如何存储选项3返回的ID并在后续的插入操作中使用它？ - Opal

这个对你有用。获取父级的ID，然后将其添加到子插入中： INSERT INTO CATEGORY (NAME) VALUES ('PARENT_1') returning id; - Ahmed Jehanzaib

@AhmedJehanzaib，是的，我知道我需要获取父级的ID。问题是如何获取？ - Opal

@Opal，还有一个问题，您在插入子项时是否知道其父项名称？ - Ahmed Jehanzaib