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

Question

我有下表:

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);

现在我需要插入数据。所以我从 parent 开始:

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，以便稍后在后续插入中使用它？

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) );