在同一INSERT期间，在另一列中引用序列列的参考值

Question

我有一个带有SERIAL主键的表格，并且还有一个ltree列，我想让它的值是这些主键的串联。例如：

id | path
----------
1    1
2    1.2
3    1.2.3
4    1.4
5    1.5

我想知道是否有一种方法可以在一次查询中完成这样的插入，例如，

INSERT INTO foo (id, ltree) VALUES (DEFAULT, THIS.id::text)

我可能在这里过度尝试，试图在一次查询中完成应该在两个查询（分组在一个事务中）中完成的工作。

- Aaron Fi

---

那个路径看起来不太适合查询。 - user166390

我的路径值基于这个：https://dev59.com/XnRB5IYBdhLWcg3wgHar#607379 - Aaron Fi

PostgreSQL 具有递归、公共表达式等功能，比您的路径解决方案/变通方法更加简单快速。 - Frank Heikens

Answer 1

您可以使用公用表表达式（CTE）仅检索一次序列值，并重复使用它：

WITH cte AS (
   SELECT nextval('foo_id_seq') AS id
   )
INSERT INTO foo (id, ltree)
SELECT id, '1.' || id
FROM   cte;

使用带有数据修改命令的CTE需要Postgres 9.1或更高版本。

如果您不确定序列的名称，请改用pg_get_serial_sequence()：

WITH i AS (
   SELECT nextval(pg_get_serial_sequence('foo', 'id')) AS id
   )
INSERT INTO foo (id, ltree)
SELECT id, '1.' || id
FROM   i;

如果表名“foo”在整个数据库的所有模式中可能不是唯一的，请加上模式限定词。如果任何名称的拼写不标准，您必须使用双引号引用它们。

pg_get_serial_sequence('"My_odd_Schema".foo', 'id')

---

快速测试表明@Mark的想法与lastval()一起使用可能也有效：

INSERT INTO foo (ltree) VALUES ('1.' || lastval());

您可以在查询中省略 id，serial 列将自动分配，不会有任何影响。

行之间不应该存在竞争条件。我引用手册上的话：“quote the manual”。

currval

返回当前会话中该序列最近一次由nextval获取的值。(如果在此会话中从未为该序列调用nextval，则报告错误。) 因为这返回一个会话本地值，所以无论其他会话是否执行了nextval，它都会给出可预测的答案。

此函数需要对序列具有USAGE或SELECT权限。

lastval

返回当前会话中nextval最近应用于的序列最近返回的值。此函数与currval完全相同，不同之处在于它不需要将序列名称作为参数，而是引用在当前会话中最近应用nextval的任何序列。如果在当前会话中尚未调用nextval，则调用lastval是错误的。

此函数需要对最后使用的序列具有USAGE或SELECT权限。

Bold emphasis mine.

但是，正如@Bernard评论所说，它仍然可能失败：没有保证在调用lastval()填充第二列ltree之前填充默认值（并在此过程中调用nextval()）。因此，请坚持使用第一种解决方案并使用nextval()来确保。

- Erwin Brandstetter

---

nextval() 会被优先调用以查找下一个主键，这样 lastval() 就不会抛出错误了吗？ - Bernard

你的第二个建议在 PostgreSQL 9.5 上会抛出一个 55000 ERROR: lastval is not yet defined in this session 的错误。 - Bernard

@Bernard：感谢您的纠正，我已经相应地进行了更新。同时添加了一种在不知道序列名称的情况下完成操作的方法。 - Erwin Brandstetter

Answer 2

这在我的测试中有效：

INSERT INTO foo (id, ltree) VALUES (DEFAULT, (SELECT last_value from foo_id_seq));

如果同时发生两个INSERT，那么这里就存在竞争条件（race condition），因为它引用的是最后一个序列值，而不是当前行。个人而言，我更倾向于使用以下伪代码：

my $id = SELECT nextval('foo_id_seq');
INSERT INTO foo (id, ltree) VALUES ($id, '$id');

- Mark Stosberg