您可以从序列中选择 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)