Postgres-使用Insert INTO SELECT语句插入自引用序列记录

我一直在尝试将数据插入自引用表，而数据来自其他不同的表。这一行属于根记录，需要有自己的引用作为外键。为了更容易理解，在此处提供表格DDL-

CREATE TABLE self_refer (
id        SERIAL  PRIMARY KEY,
parent_id INTEGER      NOT NULL,
FOREIGN KEY (parent_id) REFERENCES self_refer(id)
);

如果我们想使用直接值插入数据，这里已经讨论过这个主题，下面的语句效果很好。

INSERT INTO self_refer
(parent_id)
VALUES      ((SELECT last_value
FROM   self_refer_id_seq))

在我的情况下，我需要插入其他表中的数据，因此语句将是INSERT INTO SELECT类型。以下是我的尝试，但没有如预期的那样奏效。

INSERT INTO self_refer
(parent_id)
SELECT CURRVAL('self_refer_id_seq')

和

INSERT INTO self_refer
(parent_id)
SELECT last_value
FROM   self_refer_id_seq

具有VALUES的第一个语句适当地插入数据，但第二个和第三个语句提取last_value或currval的最新数据。第一个查询即使与currval函数一起使用也能很好地工作。

以下是前两条记录显示通过VALUES插入的结果，后两条记录使用INSERT INTO SELECT语句。

id|parent_id|
--|---------|
47|       47|
48|       48|
49|       48|
50|       49|

寻求有关如何在保持SQL语句与前两行类似的同时实现前两行的结果的任何帮助。

更新：接受的答案有效，但可能不是理想的解决方案，请仔细阅读关于如何获得更好解决方案的所有评论。

添加了以下语句以加快访问速度。如果喜欢调整和尝试其他可能性，请随时查看db fiddle。

CREATE TABLE self_refer (
id        SERIAL  PRIMARY KEY,
parent_id INTEGER      NOT NULL,
FOREIGN KEY (parent_id) REFERENCES self_refer(id)
);

✓

INSERT INTO self_refer
(parent_id)
VALUES      ((SELECT last_value
FROM   self_refer_id_seq))

1行受影响

INSERT INTO self_refer
(parent_id)
SELECT CURRVAL('self_refer_id_seq')+1;

1行受影响

select * from self_refer;

id|parent_id-：|--------：1|12|2

<blockquote\

INSERT INTO self_refer
(parent_id)
VALUES      ((SELECT last_value
FROM   self_refer_id_seq))

1行受影响

select * from self_refer;

id|parent_id-：|--------：1|12|23|3

<blockquote\

INSERT INTO self_refer
(parent_id)
SELECT CURRVAL('self_refer_id_seq')+1;

1行受影响

select * from self_refer;

id|parent_id-：|--------：1|12|23|34|4

<blockquote\

INSERT INTO self_refer
(parent_id)
SELECT last_value +1
FROM   self_refer_id_seq

1行受影响

select * from self_refer;

id|parent_id-：|--------：1|12|23|34|45|5

<blockquote\

insert into self_refer values(default, (currval('self_refer_id_seq')));

1行受影响

select * from self_refer;

id|parent_id-：|--------：1|12|23|34|45|56|6

<blockquote\

CREATE FUNCTION my_trigger_function()
RETURNS trigger AS '
BEGIN
IF NEW.parent_id = -1 THEN
NEW.parent_id := NEW.id;
END IF;
return new;
END ' LANGUAGE 'plpgsql'

✓

create trigger test_t
before insert on self_refer
for each row
EXECUTE PROCEDURE my_trigger_function()

✓blockquote\INSERT INTO self_refer
(parent_id)
SELECT -1
1行受影响

select * from self_refer;

id|parent_id-：|--------：1|12|23|34|45|56|67|7

<blockquote\

INSERT INTO self_refer
(parent_id)
SELECT 5

1行受影响

select * from self_refer;

id|parent_id-：|--------：1|12|23|34|45|56|67 | 78|5

db<>小提琴这里

这样的东西是一个选项吗：

INSERT INTO self_refer
(parent_id)
SELECT CURRVAL('self_refer_id_seq')+1;

INSERT INTO self_refer
(parent_id)
SELECT last_value +1
FROM   self_refer_id_seq

它确实有效：DEMO

在顶级父级的情况下，更常见的设计会允许列parent_id为null。这也是一个卓越的设计；至少IMHO。但是

insert into self_refer values(default, (currval('self_refer_id_seq')));

成功了。

相关内容

最新更新

热门标签：