在 Postgresql 中插入自引用记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2127156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting self-referential records in Postgresql
提问by Ovid
Given the following table in PostgreSQL, how do I insert a record which refers to itself?
给定 PostgreSQL 中的下表，如何插入引用自身的记录？
CREATE TABLE refers (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_id INTEGER NOT NULL,
FOREIGN KEY (parent_id) REFERENCES refers(id)
);
The examples I'm finding on the Web have been allowed the parent_id to be NULL and then use a trigger to update it. I'd rather update in one shot, if possible.
我在网上找到的例子允许 parent_id 为 NULL，然后使用触发器来更新它。如果可能的话，我宁愿一次性更新。
回答by wich
You can select last_value from the sequence, that is automatically created when you use type serial:
您可以从序列中选择 last_value，该序列在您使用串行类型时自动创建：
create table test (
id serial primary key,
parent integer not null,
foreign key (parent) references test(id)
);
insert into test values(default, (select last_value from test_id_seq));
insert into test values(default, (select last_value from test_id_seq));
insert into test values(default, (select last_value from test_id_seq));
select * from test;
id | parent
----+--------
1 | 1
2 | 2
3 | 3
(3 rows)
And the following even simpler seems to work as well:
以下更简单的方法似乎也有效：
insert into test values(default, lastval());
Though I don't know how this would work when using multiple sequences... I looked it up; lastval() returns the last value returned or set with the last nextval or setval call to any sequence, so the following would get you in trouble:
虽然我不知道在使用多个序列时这会如何工作......我查了一下；lastval() 返回最后一个返回或设置的值，最后一个 nextval 或 setval 调用任何序列，因此以下内容会给您带来麻烦：
create table test (
id serial primary key,
foo serial not null,
parent integer not null,
foreign key (parent) references test(id)
);
select setval('test_foo_seq', 100);
insert into test values(default, default, lastval());
ERROR: insert or update on table "test" violates foreign key constraint "test_parent_fkey"
DETAIL: Key (parent)=(101) is not present in table "test".
However the following would be okay:
但是，以下内容是可以的：
insert into test values(default, default, currval('test_id_seq'));
select * from test;
id | foo | parent
----+-----+--------
2 | 102 | 2
(1 row)
回答by Ovid
The main question is - why would you want to insert record which relates to itself?
主要问题是 - 为什么要插入与自身相关的记录？
Schema looks like standard adjacency list - one of methods to implement trees in relational database.
模式看起来像标准的邻接表——在关系数据库中实现树的方法之一。
The thing is that in most cases you simply have parent_id NULLfor top-level element. This is actually much simpler to handle.
问题是，在大多数情况下，您只需为顶级元素设置 parent_id NULL。这实际上处理起来要简单得多。
