在Postgresql中插入自引用记录 [英] Inserting self-referential records in Postgresql
问题描述
给定PostgreSQL中的下表,我该如何插入一个指向自身的记录?
Given the following table in PostgreSQL, how do I insert a record which refers to itself?
CREATE TABLE refers (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_id INTEGER NOT NULL,
FOREIGN KEY (parent_id) REFERENCES refers(id)
);
我在网上找到的示例允许parent_id为NULL,然后使用触发更新。如果可能的话,我宁愿一口气更新。
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.
推荐答案
您可以从序列中选择last_value,该序列在创建时自动创建您使用串行类型:
You can select last_value from the sequence, that is automatically created when you use type serial:
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());
虽然我不知道使用多个序列时该如何工作...我查了一下; lastval()返回最后返回的值或使用最后一次对任何序列的nextval或setval调用设置的值,因此以下操作会给您带来麻烦:
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:
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)
这篇关于在Postgresql中插入自引用记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!