在Postgresql中插入自引用记录

本文介绍了在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)