在PostgreSQL中插入自引用记录

Question

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

我在网上找到的示例允许parent_id为NULL，然后使用触发器来更新它。如果可能的话，我更愿意一次性更新。

- Ovid

---

你所说的一次操作是指一个查询还是使用事务？ - Pentium10

@Pentium10：一个问题。类似这样的语句：“INSERT INTO refers (name, parent_id) VALUES ('foo', :id)”。话虽如此，我仍然很乐意接受大家最干净的建议 :) - Ovid

我认为这在一个查询中不可能实现。我有一些禁用外键检查的编译指示，可以帮助解决非空问题。 - Pentium10

Answer 1

您可以从序列中选择 last_value，当使用类型 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)

而且下面这个更简单的似乎也可以工作：

insert into test values(default, lastval());

虽然我不知道在使用多个序列时会如何工作...但我查了一下资料; lastval()函数返回的是最后一个由任何序列的last 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".

但是以下内容是可以的:

insert into test values(default, default, currval('test_id_seq'));

select * from test;
 id | foo | parent
----+-----+--------
  2 | 102 |      2
(1 row)

- wich

---

非常好用。我使用了更明确的“test_id_seq”，只是为了安全起见。谢谢！ - Ovid

我知道它很老，但是可以保证 values(...) 的参数从左到右被评估吗？如果不是，我猜可能会出现 lastval()/select last_value ... 仍然返回上一个值的情况。 - Marcin Krupowicz

@MarcinKrupowicz 老实说，我不太确定。然而，我进行的所有测试都顺利通过了。 - wich

Answer 2

主要问题是：为什么要插入与自身相关的记录？

模式看起来像是标准的相邻列表 - 在关系数据库中实现树的方法之一。

事实上，大多数情况下，顶级元素的parent_id是NULL。这实际上要简单得多。

- user80168

---

这是一个有效的设计，对于某些人来说比相邻列表更自然，特别是在递归查询中。 - jekor

拥有一个值会迫使你拥有更完整/有效的数据。NULL值是可以接受的，但在实践中，这会导致数据质量问题或者对数据的不确定性。 - smooth_smoothie

"not null" 约束在 parent_id 列上有时是可用的 - Victor