在相同的INSERT期间，另一列中的串行列的参考值 [英] Reference value of serial column in another column during same INSERT

问题描述

我有一个带有SERIAL主键的表，还有一个ltree列，其值我想作为那些主键的串联.例如

I have a table with a SERIAL primary key, and also an ltree column, whose value I want to be the concatenation of those primary keys. e.g.

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

我很好奇是否可以在一个查询中进行这样的插入，例如

I'm curious if there's a way to do such an insert in one query, e.g.

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

我可能在这里超出了范围，试图在一个查询中做我应该在两个查询中进行的操作(在事务中分组).

I'm probably overreaching here and trying to do in one query what I should be doing in two (grouped in a transaction).

推荐答案

您可以使用CTE从序列一次中检索值，然后重复使用 :

You could use a CTE to retrieve the value from the sequence once and use it repeatedly:

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或更高版本.

The CTE with a data-modifying command requires Postgres 9.1 or later.

如果不确定序列名称，请使用 pg_get_serial_sequence() 代替:

If you are not sure about the name of the sequence, use pg_get_serial_sequence() instead:

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"在数据库中的所有模式中可能不是唯一的，请对模式进行限定.而且，如果任何名称的拼写都是非标准的，则必须双引号:

If the table name "foo" might not be unique across all schemas in the DB, schema-qualify it. And if the spelling of any name is non-standard, you have to double-quote:

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

快速测试表明 @Mark的想法与

Quick tests indicated @Mark's idea with lastval() might work too:

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

• 您只需将id保留在查询之外，即可自动分配serial列.没什么.

  • You can just leave id out of the query, the serial column will be assigned automatically. Makes no difference.

    行之间不应存在竞争条件.我引用该手册:

    There shouldn't be a race condition between rows. I quote the manual:

    currval

    返回nextval在当前会话中为此序列最近获取的值. (如果nextval具有 从未在此会话中为此顺序调用.)因为这是 返回会话本地值，它给出了可预测的答案 自当前会话以来其他会话是否已执行nextval 做到了.

    Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

    此功能需要序列上的USAGE或SELECT特权.

    This function requires USAGE or SELECT privilege on the sequence.

    lastval

    返回当前会话中nextval最近返回的值.此功能与currval相同，除了 以序列名称作为参数，它指的是任何一个 最近在当前会话中应用了序列nextval. 如果尚未调用nextval，则调用lastval是错误的 当前会话.

    Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it refers to whichever sequence nextval was most recently applied to in the current session. It is an error to call lastval if nextval has not yet been called in the current session.

    此功能需要最后使用的序列具有USAGE或SELECT特权.

    This function requires USAGE or SELECT privilege on the last used sequence.

    强调粗体.

    但是，如

    But, as @Bernard commented, it can fail after all: there is no guarantee that the default value is filled (and nextval() called in the process) before lastval() is called to fill the 2nd column ltree. So stick with the first solution and nextval() to be sure.

    这篇关于在相同的INSERT期间，另一列中的串行列的参考值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！